Extracting phone numbers from text string, variation in preceding text

philnotfil

New Member
Joined
Jun 23, 2010
Messages
37
Good Morning,
I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. I'm feeling good so far. Then someone pointed out that we have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?

Thanks,
Phillip
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

RaviWildcat

Board Regular
Joined
Jun 18, 2010
Messages
97
Good Morning,
I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. I'm feeling good so far. Then someone pointed out that we have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?

Thanks,
Phillip

So sometimes a field will say 'Office: 212-555-1212' and your logic is 'find the text startign at character 8 and catch the next 12 characters?

Why not try something like 'if the field says office:text: 212-555-1212 then find where it says text and then catch the next 12 characters?
 

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

Does this help?.....

Excel Workbook
ABCDE
1Text StringResult1Result2Failed*
2Office: 212-555-1212212-555-1212212-555-1212212-555-1212*
3office:text: 212-555-1212212-555-1212212-555-1212text: 212-555*
4*****
Sheet1


Or.............

Excel Workbook
ABCDEF
1Text StringResult1Result2Result3Failed
2Office: 212-555-1212212-555-1212212-555-1212212-555-121212-555-1212
3office:text: 212-555-1212212-555-1212212-555-1212212-555-121text: 212-555
4
Sheet1


Ak
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
Good Morning,
I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. I'm feeling good so far. Then someone pointed out that we have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?
Instead of describing the text strings using words, show us several representative samples of actual text strings (change the digits in the phone number for privacy reasons, but keep the rest of the text string intact)... that way, we will have a better idea of what your data really looks like.
 

philnotfil

New Member
Joined
Jun 23, 2010
Messages
37

ADVERTISEMENT

So sometimes a field will say 'Office: 212-555-1212' and your logic is 'find the text startign at character 8 and catch the next 12 characters?

Why not try something like 'if the field says office:text: 212-555-1212 then find where it says text and then catch the next 12 characters?

Exactly, but sometimes is says "office:" and sometimes it says "office (cell):", and I want to get them both. It looks like I may just need to set up two formulas and then combine the columns at the end.
 

philnotfil

New Member
Joined
Jun 23, 2010
Messages
37
Instead of describing the text strings using words, show us several representative samples of actual text strings (change the digits in the phone number for privacy reasons, but keep the rest of the text string intact)... that way, we will have a better idea of what your data really looks like.

Company: name, Address: address, Contact: name, Office: number, Office (Cell): number, etc.

But not all of the fields are always present, and there is no placeholder when a field has been left out. And just to make it fun, for a few entries, Office and Office (Cell) have their order switched.

Clean and consistent data makes life so much easier.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Okay, while my sense is that it may be possible to write a formula to do what you want, I cannot help but think such a formula would be humongous. I am recommending that you consider using a macro instead.
Rich (BB code):
Sub GetPhoneNumbers()
  Dim R As Long, X As Long, Col As Long, LastRow As Long, Parts() As String
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For R = StartRow To LastRow
    Parts = Split(Cells(R, "A").Value, ":")
    Col = 2
    For X = 1 To UBound(Parts)
      If InStr(1, Parts(X - 1), "Office", vbTextCompare) Then
        Cells(R, Col).Value = Left(Trim(Parts(X)), InStr(Trim(Parts(X)) & " ", " ") - 1)
        Col = Col + 1
      End If
    Next
  Next
End Sub
The only thing you may have to change is my guess at 2 for the starting row for your data (change the number in the red highlighted code line to your actual starting row number for your data).

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (GetPhoneNumbers) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.
 

philnotfil

New Member
Joined
Jun 23, 2010
Messages
37
Okay, while my sense is that it may be possible to write a formula to do what you want, I cannot help but think such a formula would be humongous. I am recommending that you consider using a macro instead.
Rich (BB code):
Sub GetPhoneNumbers()
  Dim R As Long, X As Long, Col As Long, LastRow As Long, Parts() As String
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  For R = StartRow To LastRow
    Parts = Split(Cells(R, "A").Value, ":")
    Col = 2
    For X = 1 To UBound(Parts)
      If InStr(1, Parts(X - 1), "Office", vbTextCompare) Then
        Cells(R, Col).Value = Left(Trim(Parts(X)), InStr(Trim(Parts(X)) & " ", " ") - 1)
        Col = Col + 1
      End If
    Next
  Next
End Sub
The only thing you may have to change is my guess at 2 for the starting row for your data (change the number in the red highlighted code line to your actual starting row number for your data).

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (GetPhoneNumbers) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.

Thanks, I kept fiddling with it and ended up writing two formulas, one for each variation, and then combining those columns. Not the most elegant solution, but it worked. I know little enough about VBA that code you provided is over my head, but I'll stick in my pile of stuff to play with and figure out how it works so that I can make something like it next I need it.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,981
Office Version
  1. 2016
Platform
  1. Windows
Thanks, I kept fiddling with it and ended up writing two formulas, one for each variation.
Does that mean there will always be a maximum of two phone numbers? In other words, you will never have a FAX number or Cell1 and Cell2? If so, is the text after "Office" always "(cell)" with a single space between them? If not, then is the extra text at least always surrounded by parentheses?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,555
Members
417,220
Latest member
lam150498

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
Top