# Extract 10 and 7 Digits Phone numbers from Raw data

#### donlincolnmre

##### New Member
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
3052VisitthewebsiteunderourConferenceCallsectiontoseetheschedule
30533238710329.Untitled
305510/30/
30579092411298
305910/29/
30605417632
306110/29/
30622471956
306310/28/
press Ctrl t as soon as you ope

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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...

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

{1108, 1110, 1112}

Hope this helps!

=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.

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 .

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.

If you don't mind a VBA solution,

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``

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
10VisitthewebsiteunderourConferenceCallsectiontoseetheschedule
113238710329.Untitled3238710329
1310/30/
1590924112989092411298
1710/29/
1854176325417632
1910/29/
2024719562471956
2110/28/
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.

Thanks NBVC that souluction worked fine.

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.

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

Perhaps someone can help?

Replies
11
Views
1K
Replies
3
Views
257
Replies
8
Views
267
Replies
6
Views
263
Replies
7
Views
920

1,219,914
Messages
6,150,936
Members
450,994
Latest member
MacOrch

### 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.

### Which adblocker are you using?

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

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