[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Extract number out of cell

Posted on 2014-02-07
7
Medium Priority
?
395 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

650 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