Extract 10 and 7 Digits Phone numbers from Raw data

donlincolnmre

New Member
Joined
Jun 11, 2005
Messages
46
Hello.

I have a spreadsheet in COLUMN A it contains mix data, It has 7 and 10 digits phone number and some rows have text and some date and some unmeaningfull data. I need to extract 7 and 10 digits phone number from coulumn A and put it in Coulmn B. I have posted an sample spred sheet, as you can see ROW 3051 have phone number and some data to it and row 3056 have the same problem. so i need to extract those phone nubmers

Thanks.

here is the sample data.
Expired Phone Extractor.xls
ABCD
304311/2/
30447149935165
304511/1/
30466196241038
3047withher,callon1117.11/2/
30489098824592
304911/2/
30509097904440
30514540350.LandvoiceLeads
3052VisitthewebsiteunderourConferenceCallsectiontoseetheschedule
30533238710329.Untitled
3054LandvoiceLeads
305510/30/
30567607423289 LandvoiceLeads
30579092411298
30581108,IsamsyriaDavidamericannamehe
305910/29/
30605417632
306110/29/
30622471956
306310/28/
30647143565111 1108,IsamsyriaDavidamericannameh
press Ctrl t as soon as you ope
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
I'm not sure that this will do, but assuming that the phone number is first in the text string, try the following...

A3043, copied down:

=IF(RIGHT(A3043)<>"/",IF(1-ISNUMBER(A3043),IF(1-ISNUMBER(MATCH(LEFT(A3043,4)+0,{1108},0)),LOOKUP(9.99999999999999E+307,--MID(A3043,1,{1,2,3,4,5,6,7,8,9,10})),#N/A),A3043),#N/A)

Note that if you have other values in your column such as...

1108,IsamsyriaDavidamericannamehe

...include their four digit number in the array constant. For example...

{1108, 1110, 1112}

Hope this helps!
 
Upvote 0
that didn't work, How about this formula ( i don't know much about it, but i used it to separate numeric numbers from an address)

=LEFT(A1,FIND(" ",A1,1))

since i only need to extract 1st string of character before space so the format of the data is either 1st 7 or 10 digits number and then a space and then thre rest of the text. should it should just check for first 7 digit and if the 8th character is not numberic then it should just extract that, otherwise check for 10 digits and just extract thoose 10 digits out and the rest of the data i don't need in each cell, i have about 5000 rows of data, so i will start from cell A1.
 
Upvote 0
Perhaps you can give this formula a try:

=IF(ISNUMBER(VALUE(MID(A1,10,1))),LEFT(A1,10),IF(ISNUMBER(VALUE(MID(A1,7,1))),LEFT(A1,7),""))

It looks for a digit in the 10th position first and then in the 7th position. If one is found in either position, it assumes a phone number exists and returns the first 10 or 7 characters.

If you want to get fancy and have the dashes inserted too, then use this:

=IF(ISNUMBER(VALUE(MID(A2,10,1))),LEFT(A2,3)&"-"&MID(A2,4,3)&"-"&MID(A2,7,4),IF(ISNUMBER(VALUE(MID(A2,7,1))),LEFT(A2,3)&"-"&MID(A2,4,4),""))


I'm am pretty sure these will work almost all entries based on your sample data :) .
 
Upvote 0
donlincolnmre said:
that didn't work...

I just checked it and it seems to provide the same results as the first formula offered by NBVC, except that my formula returns #N/A instead of blank when there's no phone number. Having said that, both formulas offered by NBVC follow the logic you've outlined and are more efficient.
 
Upvote 0
If you don't mind a VBA solution,

you can either add the following code to a CommandButton placed on your spreadsheet

or place it into a Public Sub that is in a Module so you can then run it using the "Tools - Macro - Macros - Run" pull-down menu

Code:
Public Sub FindTelNumbers()
Dim StartRow As Long
Dim LastRow As Long

StartRow = 10
LastRow = 31
TargetRow = 10

For i = 0 To LastRow - StartRow
    If Len(Cells(i + StartRow, 1)) > 6 Then
        If IsNumeric(Left(Cells(i + StartRow, 1), 10)) Then
            Cells(i + TargetRow, 5) = Format(Left(Cells(i + StartRow, 1), 10), "##########")
        Else
            If IsNumeric(Left(Cells(i + StartRow, 1), 7)) Then
                Cells(i + TargetRow, 5) = Format(Left(Cells(i + StartRow, 1), 7), "#######")
            Else
                Cells(i + TargetRow, 5) = "not a valid telephone number"
            End If
        End If
    Else
        Cells(i + TargetRow, 5) = "not a valid telephone number"
    End If
Next i
End Sub

Just change the Start and Last rows to match what you want. If you don't know the Last row (as it may change as more data is inserted) then use

Code:
LastRow=Range("A65536").End(xlUp).Row
 
Upvote 0
Hi donlincolnmre:

Here is a formula based solution that seems to meet your specification ...
Book1
ABCD
111/2/ 
271499351657149935165
311/1/ 
461962410386196241038
5withher,callon1117.11/2/ 
690988245929098824592
711/2/ 
890979044409097904440
94540350.LandvoiceLeads 
10VisitthewebsiteunderourConferenceCallsectiontoseetheschedule 
113238710329.Untitled3238710329
12LandvoiceLeads 
1310/30/ 
147607423289 LandvoiceLeads7607423289
1590924112989092411298
161108,IsamsyriaDavidamericannamehe 
1710/29/ 
1854176325417632
1910/29/ 
2024719562471956
2110/28/ 
227143565111 1108,IsamsyriaDavidamericannameh7143565111
Sheet4 (2)


formula in cell B1 is ...

=IF(CODE(A1)>64,"",IF(LEN(A1)<7,"",IF(LEN(A1)<8,A1,IF(OR(CODE(MID(A1,8,1))<48,CODE(MID(A1,8,1))>64),"",IF(LEN(A1)<11,A1,IF(CODE(MID(A1,11,1))<49,LEFT(A1,10)))))))

this is then copied down.
 
Upvote 0
Hello NBVC

How can i put that formula in a macro, when i coyp and paste it in the row below, the macro doesn't paste the information, how can i resolve it, since i don't want to paste the formual manually, i want the macro to paste it.

Thanks.
 
Upvote 0
I'm not sure, but I will bump this thread up for you.

Perhaps someone can help?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top