I have about 4,000 cells in Excel 200 that have phone numbers formatted as
(123)456-7890. I would like to remove everything except the numbers so that
they come out like 1234567890, what is a good way to do this?
Thanks
I have about 4,000 cells in Excel 200 that have phone numbers formatted as
(123)456-7890. I would like to remove everything except the numbers so that
they come out like 1234567890, what is a good way to do this?
Thanks
Here is one method using VBA.
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 MS Excel MVP
On Thu, 10 Aug 2006 17:34:11 -0700, "Striker" <[email protected]> wrote:
>I have about 4,000 cells in Excel 200 that have phone numbers formatted as
>(123)456-7890. I would like to remove everything except the numbers so that
>they come out like 1234567890, what is a good way to do this?
>
>Thanks
>
Striker wrote:
> I have about 4,000 cells in Excel 200 that have phone numbers formatted as
> (123)456-7890. I would like to remove everything except the numbers so that
> they come out like 1234567890, what is a good way to do this?
>
> Thanks
Here is a custom function that will do the trick for you. Create a new
Module in the VBE and enter the following code:
Function CleanNumbers(OldNumbers As Range) As String
Dim CurChr, OldStr As String
Dim Cnt As Integer
OldStr = OldNumbers
Cnt = 1
Do Until Cnt > Len(OldStr)
CurChr = Mid(OldStr, Cnt, 1)
If IsNumeric(CurChr) = True Then
CleanNumbers = CleanNumbers & CurChr
Debug.Print CleanNumbers
End If
Cnt = Cnt + 1
Loop
End Function
if A1 has the following number ---> (123)456-7890
and you use the formula: =CleanNumbers(A1) in cell A2
then A2 will display: 1234567890
Have fun....
here's one way taht may work for you
Sub test()
Dim cell As Range
For Each cell In Range("a1:a4000")
cell.Value = Replace(Replace(Replace(cell.Value, "(", "", 1), ")", "", _
1), "-", "", 1)
Next
End Sub
--
Gary
"Striker" <[email protected]> wrote in message
news:[email protected]...
>I have about 4,000 cells in Excel 200 that have phone numbers formatted as
>(123)456-7890. I would like to remove everything except the numbers so that
>they come out like 1234567890, what is a good way to do this?
>
> Thanks
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks