Hands-on: clean the phone column

Step 1 · Build the cleanup in helper columns

Never overwrite the original. Build the cleanup in helper columns so you can compare and undo at any moment.

1. In cell G2, type =TRIM(C2). This trims the phone in column C.
2. In cell H2, type =SUBSTITUTE(G2, CHAR(160), ""). This removes the non-breaking space if there is one.
3. In cell I2, type =CLEAN(H2). This strips any hidden line breaks.
4. Select G2:I2, then drag the fill handle down to row 81.

Step 2 · Inspect with LEN

In cell J2, type =LEN(I2). Drag down. Every cell should read either 0 (empty) or a length between 10 and 14 (a real phone number).

If you see any LEN value larger than 14, you still have invisible characters. Scroll to that row and check.

Step 3 · Collapse the helpers into one formula

Once you trust the helpers, replace them with a single nested formula in column K:

=SUBSTITUTE(CLEAN(TRIM(C2)), CHAR(160), "")

Drag down. Column K should match column I. If it does, you can delete G, H, and I.

Step 4 · Paste Values back into the source

Select column K. Copy. Click cell C2 (the original phone column). Edit › Paste special › Values only. Now the source column holds the cleaned values. Delete column K.