?
Solved

Extract number out of cell

Posted on 2014-02-07
7
Medium Priority
?
394 Views
Last Modified: 2014-02-10
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
Comment
Question by:cansevin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 19

Expert Comment

by:regmigrant
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

Open in new window

0
 
LVL 8

Accepted Solution

by:
Naresh Patel earned 2000 total points
ID: 39843532
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
 
LVL 8

Expert Comment

by:Naresh Patel
ID: 39843539
in this attached file I had shifted formula to column H assuming your data is in column g
SUBSTITUTE.xlsx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:Naresh Patel
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,"")," ","")

Open in new window

range J1:S1 you have to put which things you don't want in numbers.
0
 
LVL 35

Expert Comment

by:Dan Craciun
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)

Open in new window


HTH,
Dan
0
 
LVL 13

Expert Comment

by:akb
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

by:cansevin
ID: 39848333
Thanks!
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question