topic Re: data cleaning- special character in Discussions

data cleaning- special character

joann — Fri, 09 Jun 2023 00:31:59 GMT

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

Re: data cleaning- special character

txnelson — Tue, 13 Apr 2021 15:17:30 GMT

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

Re: data cleaning- special character

joann — Tue, 13 Apr 2021 15:19:59 GMT

Thank 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

txnelson — Tue, 13 Apr 2021 16:03:07 GMT

If 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 character

joann — Tue, 13 Apr 2021 16:46:23 GMT

It works! Thanks a lot Jim!!

Re: data cleaning- special character

Mark_Bailey — Tue, 13 Apr 2021 17:05:43 GMT

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 character

Georg — Tue, 13 Apr 2021 17:10:08 GMT

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 )