How-To: Remove Parentheses and Hyphens from a List of Telephone Numbers

23 views October 15, 2016 January 29, 2017 lenibbs 4

The following tip assumes that your telephone numbers start in column A1

1. Insert a new column to the right of the column containing your telephone numbers
2. Add the following formula to the first cell in the new column:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”(“,””),”)”,””),” “,””),”-“,””)
3. Copy the formula to the remaining columns by clicking on the lower right corner of the cell and dragging down

Note: Press F2 in the cell that contains the formula to reveal it. Also, you can remove the formula from the cells by copying the contents of the column to notepad.

This tip is excellent for data cleaning in preparation for use with SMS or robocall services. Note that some SMS and robocall services require data with a country code (“+1” in the US) and without parentheses or hyphens.

Was this helpful?