Modify list of phone numbers in a column...

[biffula]

I have a list of thousands of phone numbers in a column. Some look
like this:

123465 7890

I need all the spaces closed and for them to end up looking like this:

1234567890

Thanks in advance.

 

[Pete_UK]

Highlight the cells containing the phone numbers, then CTRL-H (Find &
Replace):

Find What: <space> (i.e. type one space)
Replace with: leave blank

then click Replace All. Note that this will convert the values into
numbers, rather than text, so you will loose leading zeros (if you
have any).

Hope this helps.

Pete

 

[Wigi]

Hi

Select the cells, go to Edit > Replace, and fill the textboxes such that you
change a space (just type a space in the first) box by "nothing" (leave the
second box empty). Choose to Replace All.

 

[Wigi]

Biffula

Please post your question only once.

http://www.microsoft.com/communitie...&tid=ae44534e-062e-4b3d-b765-01909dd86547&p=1

You're wasting the precious time of kind volunteers.

 

[Gord Dibben]

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

=deletenonnumerics(A1) copy down.


Gord Dibben MS Excel MVP