Removing Text but Not Numbers in a Column

  • Thread starter Thread starter canadianadmin
  • Start date Start date
C

canadianadmin

Hello I have a spreadsheet the has columns for phone numbers in it.

The Phone numbers have been entered manually and have all differen
formats

such as (555)123-1234 or 555-123-1234

I need to delete all of the text so that just the numbers are left s
it looks like 5551231234, so I can upload the column into my cel
phone. Would there be an easy way of doing this with out manuall
changing each entry I have over 2500 phone numbers I have to do thi
to.


I try the Format Painter, and Formatting the Cells but it didn't wor
for me.


I am running Microsoft Office For OS X, and any help would be greatl
appreciated

Thank
 
Hi
for your example try the formula
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")",""),"-","")
 
Basically,

The only way I can think of will take 4 steps, all using th
Edit-Replace function.

1. Select the whole column of numbers and [edit][replace] "(" wit
(leave blank).

2. Repeat for ")".

3. Repeat for "-".

4. Repeat for (type a space).

Also repeat for any Periods or other special characters. This shoul
leave all your numbers in a straight nine-digit text format
 
Are you up for a macro?

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Gord Dibben Excel MVP
 
rbanks said:
*Basically,

The only way I can think of will take 4 steps, all using th
Edit-Replace function.

1. Select the whole column of numbers and [edit][replace] "(" wit
(leave blank).

2. Repeat for ")".

3. Repeat for "-".

4. Repeat for (type a space).

Also repeat for any Periods or other special characters. This shoul
leave all your numbers in a straight nine-digit text format. *
Click to expand...


Thanks alot, It worked great, I'm sorry if this seemed like a stupi
question but I am not too comfortable with excel, and I didn't try an
of the other ways because they seemed to much programming for me, th
limit of my programming skills is basically limited to programmin
Cisco Routers

Anyways thanks alot for all of the suggestion
 
Back
Top