Pulling specific data from a cell with a paragraph in Excel

Question

Hello so here's my issue i have a large spreadsheet export from outlook with some data that i want to retrieve from the notes column.

since when outlook exports contact info it puts all the text from the notes field in one cell per person i'd like to be able to pull prices and phone numbers that were in the notes field for each person and place them in their own column respectively.

the problems I'm coming across are that the paragraphs will be of varying lengths and there may be multiple phone numbers or prices within the paragraph as well.

I've tried using =MID(A1,FIND("$",A1),8) to get the price out however the prices may vary in length.

I'm not sure how to get the phone numbers out either.

sorry I'm a little new at this kinda stuff so forgive my ignorance if I've left something out.

(edit)

The column of cells has the kind of info below and I'm trying to get the $ amount and the phone number out and into their own columns each cell will have different variations and lengths of notes.

"04/08/13 – ****** E-mailed and called Joshua on 04/08/13 and wanted to sign up to the *** Marketing program for CDN $399.00 + HST. Does want the text messaging market quote program. at 306 555 5555 service provider sasktel 04/22/13 – ***** ***** sent e-mail asking for username and password and **** sent him an e-mail."

Currently I'm using the above formula to pull the price then i manually enter it into the field it belongs. For the phone number and any other info, I transcribe one cell at a time.

Answer 1

You need Regular Expressions to work with that kind of data.

First you will need to add a module to your VBA project and paste in the following code:

Public Function RegexReplace(needle, haystack, replacement)

    Dim regex
    Set regex = CreateObject("VBScript.RegExp")

    regex.Pattern = needle
    regex.Global = true
    regex.IgnoreCase = true

    RegexReplace = regex.Replace(haystack, replacement)

End Function

Price: =RegexReplace(A1, "[^\$]+([0-9\.]+).+", "$1")

Phone number: =RegexReplace(A1, ".+([0-9]{3} [0-9]{3} [0-9]{4}).+", "$1")

Both of these regex's are fairly naive, and would be extremely fragile. They both will only match if the price and phone number are formatted exactly as in your example.

Here are a couple previous questions asked on the topics of matching phone numbers, and currency.