Hi all, I have a huge list of phone numbers, and I want them to be listed as numbers without dashes or parenthises (e.g. 1234567890) so that I can format them all how I want. How can I go about doing this? The code below is all that I could come up with, but I know almost nothing about the Characters property.
Public Sub fff()
For Each character In cell.Characters
If Not IsNumeric(character) Then
character.Delete
End If
Next character
End SubThanks!
Re: Delete non-numeric characters (like symbols) from cells
Are you numbers all formatted the same? i.e., like (###) ###-####
Re: Delete non-numeric characters (like symbols) from cells
About half are
###-###-####
Others are
###/###-####
###.###.####
(###)###-####
### ### ####
And some other strange combinations of these symbols.
Re: Delete non-numeric characters (like symbols) from cells
Try this formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"-",""),":",""),"/",""),"_",""),"(",""),")","")
If there are additional characters not covered in the formula, just add them to the formula.
Re: Delete non-numeric characters (like symbols) from cells
Awesome, works perfectly! Thanks guys
Re: Delete non-numeric characters (like symbols) from cells
Here is a little UDF that allows easier edits, kind of think that You want in the xls of the tools You use - edit 'myNotAllowed_DeleteThem' for as many as You need to be deleted
jiuk
[vba]
Dim i As Integer
Public Function myCleanString(strString As String)
' written by: Jack in the UK - http://www.excel-it.com
' for http://www.OzGrid.com
Const myNotAllowed_DeleteThem = "-/."
For i = 1 To Len(myNotAllowed_DeleteThem)
strString = Application.WorksheetFunction.Substitute(strString, _
Mid(myNotAllowed_DeleteThem, i, 1), "")
Next i
myCleanString = strString
End Function
[/vba]
Marker No Nos
Re: Delete non-numeric characters (like symbols) from cells
Jack,
This is really awesome. Thanks !
Don’t have an account yet? Register yourself now and be a part of our community!