• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Extract number out of cell

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
cansevin
Asked:
cansevin
1 Solution
 
regmigrantCommented:
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

Open in new window

0
 
Naresh PatelTraderCommented:
try this I had
=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,"")," ","")

Open in new window


in this you can put your desire character with you want to omits from numbers in range I1:R1.
SUBSTITUTE.xlsx
0
 
Naresh PatelTraderCommented:
in this attached file I had shifted formula to column H assuming your data is in column g
SUBSTITUTE.xlsx
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Naresh PatelTraderCommented:
=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,"")," ","")

Open in new window

range J1:S1 you have to put which things you don't want in numbers.
0
 
Dan CraciunIT ConsultantCommented:
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)

Open in new window


HTH,
Dan
0
 
akbCommented:
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.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now