Removing only certain data in a cell

[Guest]

If I have a cell that contains the following, (Phone 123-456-7890), is there
a formula I can use that will strip the number off and put it in another
cell? I have a big list I need to clean up, and doing it one at a time will
take to long. Thanks

 

[Frank Kabel]

Hi
see:
http://www.dicks-blog.com/archives/...ion-with-worksheet-formulas-part-1/trackback/

 

[Guest]

A help column and

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Phone",""),"(",""),")",""))

and if you don't want the hyphens

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Phone",""),"(",""),")",""),"-",""))

and if you want a real number

=--TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Phone",""),"(",""),")",""),"-",""))

Of course you might be best off just using edit>replace

Regards,

Peo Sjoblom

 

[Gord Dibben]

Juan

Macro to strip all but 123-456-7890.

Copy the original column to an adjacent column then run this macro on the
copied column.

If you don't care about anything but the 123-456-7890 then don't bother
copying the column.

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

 

[CLR]

=RIGHT(A1,12) and copy down............

Vaya con Dios,
Chuck, CABGx3