Solved

# Extract number out of cell

Posted on 2014-02-07
381 Views
I have a column of phone numbers that are all in different forms, such as:

(555) 123-4567
555-123-4567
555.123.4567
555 123 4567

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.

Thanks!
0
Question by:cansevin

LVL 19

Expert Comment

ID: 39843505
a couple of quick and dirty methods:-

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 String

Dim TextLen As Integer
Dim i As Integer
Dim n As String

TextLen = Len(text)
For i = 1 To TextLen

n = Mid(text, i, 1)
If Asc(n) > 57 Or Asc(n) < 48 Then n = ""

numTEXT = numTEXT & n

Next i
End Function
``````
0

LVL 8

Accepted Solution

itjockey earned 500 total points
ID: 39843532
``````=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,\$I\$1,""),\$J\$1,""),\$K\$1,""),\$L\$1,""),\$M\$1,""),\$N\$1,""),\$O\$1,""),\$P\$1,""),\$Q\$1,""),\$R\$1,"")," ","")
``````

in this you can put your desire character with you want to omits from numbers in range I1:R1.
SUBSTITUTE.xlsx
0

LVL 8

Expert Comment

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

LVL 8

Expert Comment

ID: 39843542
``````=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G1,\$J\$1,""),\$K\$1,""),\$L\$1,""),\$M\$1,""),\$N\$1,""),\$O\$1,""),\$P\$1,""),\$Q\$1,""),\$R\$1,""),\$S\$1,"")," ","")
``````
range J1:S1 you have to put which things you don't want in numbers.
0

LVL 34

Expert Comment

ID: 39843719
Here's another way:
``````H2: =SUMPRODUCT(MID(0&G2,LARGE(INDEX(ISNUMBER(--MID(G2,ROW(\$1:\$20),1))*ROW(\$1:\$20),0),ROW(\$1:\$20))+1,1)*10^ROW(\$1:\$20)/10)
``````

HTH,
Dan
0

LVL 13

Expert Comment

ID: 39843731
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

Author Closing Comment

ID: 39848333
Thanks!
0

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.