Searching Text String From The Right End

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
try this one

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

-----Original Message-----
I have a column of cells, each cell containing a text
Click to expand...
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, 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
Click to expand...
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?
 
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?
Click to expand...

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)
 
Hi
or try a non array formula
=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,1024)
 
Frank Kabel said:
or try a non array formula
=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,
" ",""))))+1,1024) ....
Click to expand...

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.
 
Harlan Grove said:
Frank Kabel said:
or try a non array formula
=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1,
" ",""))))+1,1024) ...
Click to expand...
[...]
=MID(TRIM(A1),FIND("^",SUBSTITUTE(TRIM(A1)," ","^",
LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))))+1,1024)
Click to expand...

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.
Click to expand...

O.K. you beat me here

Frank
 
Back
Top