Text functions

[George]

I am using xp pro and excel 2000. Trying to auto strip
text. Cell contents can be (mr a post) or (john life) or
(a peolpe). I would like to be able to strip out the last
name but all the functions seem to go from left to right?

 

[Frank Kabel]

Hi
try:
=MID(A1,LOOKUP(2,1/(MID(" "&A1,seq,1)=" "),seq)-1+1,1024)
where seq is a defined name with the formula =ROW(INDIRECT("1:1024"))

or use
=MID(A1,FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN("
"&A1)-LEN(SUBSTITUTE(" "&A1," ",""))))-1+1,1024)

 

[Nitin Harkawat]

Frank
What does Lookup(2,1/ .... function do Can you please explain?
Thanks in advance

 

[Frank Kabel]

Hi
the interesting part is the second parameter of LOOKUP:
1/(MID(" "&A1,seq,1)=" ")

depending on the string in column A this returns for each character
(that is for what I use MID)
1/(TRUE)
1/(FALSE)

TRUE in allcases there the character matches " " and fALSE ina ll other
cases

If you use boolean values in a mathematical operation Excel coerce them
to real numbers (TRUE=1, FALSE=0). So the above would be converted to
1/(1)=1
1/(0)=#DIV/0

So the 2nd parameter returns an array of '1' and #DIV/0 errors for each
character. Only the spaces will show as '1', all other charaters as
error value. As LOOKUP now searches for the value 2 it returns the last
element which
is smaller or equal (and that is the last '1' in this range). This way
it returns the last position of a space character

 

[Nitin Harkawat]

Thanks for the explaination Frank.Really appreciate your input.
Nitin