An maybe even a few more random forms. None of them ever have a 1 in front of area code, they all have 10 numbers.

Ultimately I need to run a Remove Duplicates from this column. I was thinking of creating a column right next to it that has all the phone numbers as 1234567890... just 10 numbers. Then running the remove duplicates from that column.

How would I get it to 123456790? The phone numbers are in column G.

use find, replace multiple time until the 'extra' characters are gone

use Susbstitute function as follows:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A5,".",""),"(",""),")",""),"(","")," ",""),"(",""),")",""),"(","")

unfortunately that's the most levels of nesting allowed so you would need a further column to remove any other missing characters

A less clunky approach is to put the following code into a module and call it with
=numtext(g1) then copy down (assuming your phone numbers start in row 1)
this wont allow anything except numbers through

Function numTEXT(text) As StringDim TextLen As IntegerDim i As IntegerDim n As StringTextLen = Len(text)For i = 1 To TextLenn = Mid(text, i, 1)If Asc(n) > 57 Or Asc(n) < 48 Then n = ""numTEXT = numTEXT & nNext iEnd Function

in this attached file I had shifted formula to column H assuming your data is in column g SUBSTITUTE.xlsx

0

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

1. In Excel, with your workbook open, press Alt+f11 this will take you to the VBa Editor.

2. With the "Insert" Tab choose "Module"

3. In the resulting window paste all of the following code

Function CleanCode(Rng As Range)
Dim strTemp As String
Dim n As Long
For n = 1 To Len(Rng)
Select Case Asc(Mid(UCase(Rng), n, 1))
Case 48 To 57
strTemp = strTemp & Mid(UCase(Rng), n, 1)
End Select
Next
CleanCode = strTemp
End Function

Assuming data is in A1, in Excel use:
=CleanCode(A1)

0

cansevinAuthor Commented:

Thanks!

0

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

use find, replace multiple time until the 'extra' characters are gone

use Susbstitute function as follows:

=SUBSTITUTE(SUBSTITUTE(SUB

unfortunately that's the most levels of nesting allowed so you would need a further column to remove any other missing characters

A less clunky approach is to put the following code into a module and call it with

=numtext(g1) then copy down (assuming your phone numbers start in row 1)

this wont allow anything except numbers through

Open in new window