Community Manager
We have multiple options to clean up inconsistent phone entry data in JMP. One is the Formula Editor with functions like Concatenate and Word functions; however, I usually start with JMP's Recode utility. In this example data table of 6 different phone number formats entered by users, I'll right-click on the Column and select Recode. In this quick demo, my goal is to eliminate all non-numeric characters. The great thing about Recode is I can stack the changes, then perform those multiple changes all at once, and even save it to a Script for even more powerful formatting.
I have 3 ways you can accomplish this in Recode. Two of those I'm going to demo, and the third is using a Regular Expression or Regex, but that is a topic for an advanced video in the JMP Community.
Let's start with Replace String. So first, I select New Column for my New Data Values, then from the red triangle select Replace String. Then, I'll type in my dash and replace it with a blank entry to eliminate those. Then, I'll enter in the "+ " and replace that with a blank entry. And finally, I'll eliminate the Parentheses in two additional changes. So now I have four changes total, and I'll select Recode. The second faster approach is Remove Punctuation. Selecting Remove Punctuation eliminates all non-numeric at once. Maybe I should have started with that, but now you know both and know how to create multiple changes in Recode. You can also use an Expression by toggling that on and slowly go through all the powerful options under the Red Triangles here in Recode. And now in my new, in my New Value Column, I have only Numeric information. From there, I can split the numbers and further process this data using a Formula. But this will get you started with processing phone numbers. There's more work to do with missing or existing country values, but Recode could be your first powerful step. After trying these methods, move on to learning and creating expressions.
When the video on using expressions in Recode is done I'll link it here.