Solved

Pull phone numbers out of cell

Posted on 2014-07-25
3
157 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
  • 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

785 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