376249_en-US

liliconcom

data cleaning- special character

Hi, I have a messy column "zip code" filled with all kinds of special characters. Is there a way I can quickly remove all the special character and leave the string using JMP's function but not jsl? Thank you. 

 

AZ23105

,06385

[29466]

@11768

*****10065

AnalyticsRe: data cleaning- special character

Just another approach using regex, but not so easy to understand:

It replaces all occurrences in :column1 that are not digits by "".

Regex( :Column 1, "[^0-9]", "", GLOBALREPLACE )

 

Re: data cleaning- special character

You should also look at the Rows > Recode command. It has been significantly enhanced over several recent versions of JMP. Many problems with data (e.g., inconsistency) can be directly handled with the Recode tool now without resorting to formulas or scripts.

Re: data cleaning- special characterIt works! Thanks a lot Jim!!Re: data cleaning- special characterIf you make the column a character column, and then change the last line in the formula from
Num( Number );
to just
Number;Re: data cleaning- special characterThank you Jim! Is there a way to keep the 0 in front of the string? like "06385" but not "6385".Re: data cleaning- special character

I am not aware of a JMP builtin function that will convert the data as needed.  However attached is a data table where I have created a new formula column that will remove all non numeric values