Searching Text String From The Right End

[Guest]

I have a column of cells, each cell containing a text string which includes titles, first names, middle names, last names. Not all appear in each cell. I want to pull out the last name. I'd like to search in from the right end of the cell, looking for the first space. The find command performs this function if I search from the left end of the string. Is there a similar command that searches from the right end?

 

[macky]

try this one

=RIGHT(B14,FIND(" ",B14))

-----Original Message-----
I have a column of cells, each cell containing a text

string which includes titles, first names, middle names,
last names. Not all appear in each cell. I want to pull
out the last name. I'd like to search in from the right
end of the cell, looking for the first space. The find
command performs this function if I search from the left
end of the string. Is there a similar command that
searches from the right end?

 

[Jason Morin]

No, but you could use:

=MID(TRIM(A1),FIND("#",SUBSTITUTE(TRIM(A1)," ","#",LEN(TRIM
(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))))+1,255)

HTH
Jason
Atlanta, GA

-----Original Message-----
I have a column of cells, each cell containing a text

string which includes titles, first names, middle names,
last names. Not all appear in each cell. I want to pull
out the last name. I'd like to search in from the right
end of the cell, looking for the first space. The find
command performs this function if I search from the left
end of the string. Is there a similar command that
searches from the right end?

 

[Harlan Grove]

I have a column of cells, each cell containing a text string which includes
titles, first names, middle names, last names. Not all appear in each cell.
I want to pull out the last name. I'd like to search in from the right end of
the cell, looking for the first space. The find command performs this
function if I search from the left end of the string. Is there a similar
command that searches from the right end?

No, there's no single function that searches right to left. However, it's simple
to improvise. Define the name Seq referring to =ROW(INDIRECT("1:1024")). Then
try the array formula

=MID(TRIM(X99),MAX(IF(MID(TRIM(X99),Seq,1)=" ",Seq))+1,1024)

 

[Harlan Grove]

try this one

=RIGHT(B14,FIND(" ",B14))

And what would that return for the name

Jan Kraznopolski

 

[Frank Kabel]

Hi
or try a non array formula
=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,1024)

 

[Harlan Grove]

or try a non array formula
=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,
" ",""))))+1,1024) ....

I didn't put the TRIM calls in my formula just to waste cycles. They're a
precaution meant to trap stray trailing blanks. If you had followed this
sound practise, your formula would have been

=MID(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1)," ","^",
LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,1024)

[Exercise: why don't you need a TRIM call in the second LEN call?]

That'd make for 9 function calls, which is one fewer than my formula above
after counting the implicit ROW and INDIRECT calls in each Seq reference. I
can do better than that, so with the defined names N referring to 1024, Seq
referring to =ROW(INDIRECT("1:"&N)), the array formula

=MID(TRIM(X99),N+2-MATCH(" ",MID(TRIM(X99),N+1-Seq,1),0),N)

uses only 7 explicit and implicit function calls. There are times when it
makes sense to avoid array formulas, but not when it takes two more function
calls to avoid them.

Other alternatives: with Longre's MOREFUNC.XLL, =WMID(X99,-1,1); with a
regular expression udf similar to the SUBSTITUTE function (such as the Subst
udf I've posted in the past), =Subst(A1,"^.*\s(\S+)\s*$","$1"), which is
much longer than WMID, but MUCH more flexible in case such things as
multiple word last names (e.g., de la Cruz) should be accomodated but
trailing generational qualifiers (e.g., Jr.) should be excluded.

 

[Frank Kabel]

or try a non array formula
=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,
" ",""))))+1,1024) ...

[...]
=MID(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1)," ","^",
LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,1024)

agreed, should have used TRIM

[Exercise: why don't you need a TRIM call in the second LEN call?]


[...]
=MID(TRIM(X99),N+2-MATCH(" ",MID(TRIM(X99),N+1-Seq,1),0),N)

uses only 7 explicit and implicit function calls. There are times when it
makes sense to avoid array formulas, but not when it takes two more function
calls to avoid them.

O.K. you beat me here

Frank

 

[Guest]

Thank you one, thank you all. Very ingenious. Does exactly what I wanted.