Match? Index? Lookup? Which to use?

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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

Active Member
Joined
Jul 14, 2005
Messages
333
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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
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-known Member
Joined
Jan 13, 2005
Messages
5,219
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
Ah... a bit more light on the subject... :wink:

Like so perhaps?
book1
ABCD
1Lewis, Randy
2 <== helper cell123 Roadway Drive
3 <== using helper cellYourville, SC 98765
4Bus: (212) 299-8888
5 <== without help cellMobile: (212) 222-4444
6Bus 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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
9,999
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]
 

Forum statistics

Threads
1,078,012
Messages
5,337,724
Members
399,165
Latest member
ghoshwin

Some videos you may like

This Week's Hot Topics

Top