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.

 

[sen_edp]

YOU TRY THAT TOO, IT WORKS !!

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,A1,RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))

RGDS

ANDREAS

 

[Aladin Akyurek]

On 2002-05-01 15:10, zacemmel wrote:
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))}

Change it so that it has a more usual ROW arg:

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

It requires TRIM around it too, considering "sometimes spaces, sometimes not".

Aladin

 

[zacemmel]

Yes, but when yours didn't have spaces, it returned #VALUE! I actually got a result with mine that wasn't an error
This message was edited by zacemmel on 2002-05-01 16:13

 

[Aladin Akyurek]

On 2002-05-01 16:12, zacemmel wrote:
Yes, but when yours didn't have spaces, it returned #VALUE! I actually got a result with mine that wasn't an error
This message was edited by zacemmel on 2002-05-01 16:13

You missed the point of my comment. And, I wasn't comparing it with anything.

Moreover, I told you the formula I suggested can accomodate "," as delimiter. It will need a TRIM too to cope with possible existence of spaces:

=TRIM(REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1,",","@",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))),""))

 

[zacemmel]

Oops.