Match? Index? Lookup? Which to use?

[daniels012]

I have data I get from Outlook I want to enter into Excel.
I copy all client data to a blank sheet. I then want to look for the word Mobile: and have it list the data to the right of the colon. For example:

Lewis, Randy
123 Roadway Drive
Yourville, SC 98765
Bus: (212) 299-8888
Mobile: (212) 222-4444
Bus Fax: (212) 333-5555

Basically I want it to find Bus: then give me the data in a cell &
Mobile: Give me the data
Bus Fax: Give me the data

I have been trying Match, but with no success. I know I can't us lookup becasue it has to be sorted. Any ideas?

Thank You,
Michael

 

[colbymack]

Are the data rows always in the same format?
i.e.
Name
Addr1
Addr2
Busphonenumber
mobilephonenumber
faxnumber

Also, if someone does not have one of these, does it still have a blank?

i.e.
Bus: (212) 299-8888
Mobile:
Bus Fax: (212) 333-5555

Thanks,
Colbymack

 

[daniels012]

Yes, the data shows in the same order each time.
No, nat all data shows everytime. ie: cell number may be there or may not.

That is why I know I have to use MID or MATCH or something to lookup the data. It will always have the word say for example Mobile.

Michael

 

[daniels012]

Is there a way to search this? Easily??

Michael

 

[Greg Truby]

I'd just highlight the three cells in question and quickly hit Data | Text to Columns... -> Delimited -> Other=colon -> text + text -> Finish and bang, you're done.

 

[daniels012]

Well that is fine, but I have to have the data in a certain cell in the spreadsheet I am entering it into.

So I need it to copy the mobile number and put it into cell A6 for example.

So I need to separate each one using some sort of lookup formula??

Thank You,
Michael

 

[Greg Truby]

Ah... a bit more light on the subject... :wink:

Like so perhaps?

book1
	A	B	C	D
1			Lewis, Randy
2	Mobile: (212) 222-4444	<== helper cell	123 Roadway Drive
3	(212) 222-4444	<== using helper cell	Yourville, SC 98765
4			Bus: (212) 299-8888
5	(212) 222-4444	<== without help cell	Mobile: (212) 222-4444
6			Bus Fax: (212) 333-5555
Sheet1

Formulae:<ul>[*]A2 =INDEX($C$1:$C$6,MATCH("mobile*",$C$1:$C$6,0))[*]A3 =RIGHT(A2,LEN(A2)-FIND(":",A2,1))[*]A5 =RIGHT(INDEX($C$1:$C$6,MATCH("mobile*",$C$1:$C$6,0)),LEN(INDEX($C$1:$C$6,MATCH("mobile*",$C$1:$C$6,0)))-FIND(":",INDEX($C$1:$C$6,MATCH("mobile*",$C$1:$C$6,0)),1))[/list]

 

[daniels012]

Greg... Thank You.
That is amazing.
It would have taken me a year to come close to this answer.

One thing, Is there a way to have it not show #N/A when say the Mobile: number never shows up??

Thank You,
Michael

 

[Greg Truby]

Much easier if we use just a wee bit of VBA to help us out. Add in a UDF called v(). The code for v() is here. Then edit the formula to be:<ul>[*]=IF(ISNA(v(INDEX($C$1:$C$6,MATCH("mobile*",$C$1:$C$6,0)))),"no cell phone",RIGHT(v(),LEN(v())-FIND(":",v(),1)))[/list]<hr>If you have never added a UDF...

To add a User Defined Function (UDF) to a workbook:

1. Alt+F11 to get to the Visual Basic Editor (VBE).
2. If WB does not contain any Standard Modules then from the VBE menu Insert|Module
3. Add in code (copy and paste).
4. Alt+F11 to jump back to Excel.
5. In the cell enter the formula using the newly created UDF. Example<ul type=square>
6. =myNewUDF()

[/list]