Searching and splitting cell values.

[Simon]

I have a problem that I guess in part VLOOKUP and part
SEARCH.

I want to enter a String in to a cell with various values,
they will always be names eg.

Fred Smith

However I have a table formatted as below:

Col 1 Col2
White, James 4e.231
Red, John 1w.214
Smith, Fred 5w.142

I need to find the name that was input, within the table
and return the first part of Col2 (before the decimal) to
other column and the second part of Col2 (after the
decimal) to a seperate column.

So typing

Fred Smith

would result in

ColX ColY
5w 142

I hope this makes sense.
Please can anyone help?

Many Thanks,

Simon

 

[Frank Kabel]

Hi
if you enter Fred Smith in cell W1 try the following formula in X1
=LEFT(VLOOKUP(TRIM(MID(W1,FIND(" ",W1)+1,255) & ", " & LEFT(W1,FIND("
",W1)-1)),$A$1:$B$1000,2,0),FIND(".",VLOOKUP(TRIM(MID(W1,FIND("
",W1)+1,255) & ", " & LEFT(W1,FIND(" ",W1)-1)),$A$1:$B$1000,2,0)-1)

And in Y1 enter
=MID(VLOOKUP(TRIM(MID(W1,FIND(" ",W1)+1,255) & ", " & LEFT(W1,FIND("
",W1)-1)),$A$1:$B$1000,2,0),FIND(".",VLOOKUP(TRIM(MID(W1,FIND("
",W1)+1,255) & ", " & LEFT(W1,FIND(" ",W1)-1)),$A$1:$B$1000,2,0)+1,255)