Solved

Pull phone numbers out of cell

Posted on 2014-07-25
3
160 Views
Last Modified: 2014-10-10
I have a column of data that has memo's in them. Short paragraphs are in each cell.

I would like to try to pull out any phone number listed in the memo in the next column. The phone numbers can be of any format ie: xxx.xxx.xxxx or (xxx) xxx-xxxx or xxxxxxxxxx.

If easier, I can write something for each format. For example pull out any phone number in the format (XXX) XXX-XXXX. Then another for any in the format XXX.XXX.XXXX. Then another for XXX-XXX-XXXX. Then one for XXX-XXX-XXXX.

Any way to make that happen? This could save me HOURS of time!
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
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Flyster earned 500 total points
ID: 40220855
Will those be the only numbers in that string? If so you can try this:
=SUM(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)

Open in new window

This is an array formula so after you paste it, hit ctrl+shift+enter. Change "A1" to your target cell.
Flyster
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40221011
^^^ That is one amazing function, Flyster.  The only possible issue is if other numbers exist, but I bet this will work for the questioner in almost all cases.
0
 
LVL 22

Expert Comment

by:Flyster
ID: 40222073
@Glenn Ray. Thanks, but I really can't take credit for it. I came across it several years ago when I was looking to strip the numeric from a string. I only wish I would have documented where I got it from! You are correct. It will list all the numbers in that string.
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

691 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