Cleaning phone numbers
Clean phone numbers of unwanted characters, such as parentheses or plus signs
Some services, like Facebook Ads, require phone number data that you upload to be in a certain format. Cleaning your data to fit their criteria can be difficult and many people have to do it on a continuous basis. In Excel, you could apply a series of formulas to your data.
Using the Find/Replace Object is an easy way to add a series of rules for removing or replacing unwanted characters. The object operates over a single column, yet our data is in multiple columns, so we will need to transform it into the proper form before applying the Find/Replace Object.
First, we use the Row Numbers Object to add temporary ID's. Then, we filter out unnecessary columns, only keeping the ID and phone number columns. From there, we use the Unpivot Object to stack all of the phone numbers into a single column. Now we can apply the cleaning rules from the Find/Replace Object. To return the data to its original form, we use the Pivot Object to create multiple columns for phone numbers, and join it back to the original data with a Join Object.