Interesting formula

[zacemmel]

I want to return the name "Tom" from cell A1 where cell A1 contains "Harry, Sally, Bob, Jack, Tom."

I am just curious how other board members have approached this problem.

 

[zacemmel]

No, I want the last name of the cell. I don't want to find tom. I want to find the last name in the series in that cell.

ie cell A1 contains

sally, fred, jack, tom

I want tom in that case

ie cell A1 contains

fred, sally, chris

I want chris in that case

Does that make it any clearer?

Thanks for the help.

 

[Yogi Anand]

To further generalize it -- if the entry to be searched for is in cell A3, then use

=MID(A1,FIND(A3,A1,1),LEN(A3))

Regards!
Yogi Anand

 

[Aladin Akyurek]

On 2002-05-01 14:53, zacemmel wrote:
No, I want the last name of the cell. I don't want to find tom. I want to find the last name in the series in that cell.

ie cell A1 contains

sally, fred, jack, tom

I want tom in that case

ie cell A1 contains

fred, sally, chris

I want chris in that case

Does that make it any clearer?

Thanks for the help.

See my reply...

 

[zacemmel]

Sometimes there are spaces, sometimes there aren't. I wrote my own formula though Thanks!

 

[zacemmel]

In case you are curious, I did an array formula:

{=MID(A1,MAX(IF(MID(A1, ROW(A1:A1000),1)=",", ROW(A1:A1000), -99999))+1,LEN(A1))}

 

[Aladin Akyurek]

On 2002-05-01 15:05, zacemmel wrote:
Sometimes there are spaces, sometimes there aren't. I wrote my own formula though Thanks!

Even then (with these additional specs) the formula I jotted down slightly modified to take "," as delimiter will do.

 

[zacemmel]

Thanks Aladin.

 

[Yogi Anand]

Also try

=MID(A1,FIND("~",SUBSTITUTE(A1,",","~",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))),1)+1,LEN(A1))

Regards!
Yogi Anand

 

[davers5]

Or you could create your own funtion to do this:

Function FindLastValue(List As Variant) As Variant
FindLastValue = trim(Mid(List, InStrRev(List, ",") + 1, Len(List) - InStrRev(List, ",") + 1))
End Function

And then say FindLastValue(a1).

 

[zacemmel]

Arrays are so much more powerful than replaces