RIGHT () not working
Rick Campbell
I'm using the following to extract the last name from a full name field:
RIGHT(C2,SEARCH(" ",C2))
It's supposed to check to the first space and return everything from there.
But it ain't! The results have no consistency to them:
This is a sample of some original fields and the results:
Original
Carlos & Silvia Aceves
Kelli Adams
Josephine Adamson
Ardath Albee
Marilyn Alexander-Readers
Les Alhadeff
Results
Aceves
Adams
ne Adamson
h Albee
-Readers
deff
Is my formula wrong? It works for LEFT (). Is there a bug in Excel? Help!!!
--
Rick Campbell
Information Designs
415.460.1720
ide...@pacbell.net
Nick Manton
Hi Rick,
What your formula does is to search for the _first_ space, and count how
many characters in that is, and then returns that many characters from the
right, so unless the first and last names are the same length, it won't give
you the correct answer.
HTH,
Nick
--
Nick Manton
Chemistry & Chemical Engineering Labs.
SRI International, Menlo Park, CA
ni...@sri.com
Rick Campbell wrote in message ...
Ogilvy, Thomas W., Mr., ODCSLOG
Rick,
Your formula doesn't work because search is operating from the Left and
Right is counting from the right. The places it works is coincidence.
In the first name, it finds the first space 7 characters from the left
which corresponds to the last space which is 7 characters from the
right. Just luck. Here is a formula that works:
=RIGHT(C2,LEN(C2)-MAX(IF(NOT(ISERR(SEARCH(" ",C2,ROW(1:50)))),SEARCH("
",C2,ROW(1:50)))))
It produces this:
Carlos & Silvia Aceves Aceves
Kelli Adams Adams
Josephine Adamson Adamson
Ardath Albee Albee
Marilyn Alexander-Readers Alexander-Readers
Les Alhadeff Les Alhadeff
From you data.
It is an array formula. Paste in the formula into the formula bar of
the cell adjacent to the top name in your list (where you want the last
name to appear) and then hit Ctrl+Shift+Enter instead of enter as you
normally would. The formula will now appear with braces {} around it to
indicate Excel recognizes it as an array formula. If you ever edit the
formula, you need to hit Ctrl+Shift+Enter to keep it as an array
formula.
Now you can select the cell and drag fill down the column. The
remainder of the names will be parsed.
HTH,
Tom Ogilvy
> ----------
> From: Rick Campbell[SMTP:ide...@pacbell.net]
> Posted At: Thursday, May 28, 1998 5:19 PM
> Posted To: worksheetfunctions
> Conversation: RIGHT () not working
> Subject: RIGHT () not working
Rick Campbell
Tom,
Thanks for the formula. I had to change the array to absolute and then it
worked perfectly.
Rick Campbell
Thanks Nick,
You've explained the seeming inconsistency in the formula. Perfectly
logical.
cf...@earthlink.net
=RIGHT(C2,LEN(C2)-SEARCH(" ",C2)) will return everything to the right of the
first space. More work needed to deal with a middle initial.
Hope this helps.
Charlie
Rick Campbell wrote in message ...
David Hager
Thomas,
Here is a somewhat simpler formula that ulitizes the same approach that you
used.
=MID(A1,MATCH(TRUE,ISERR(FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1))))),0),255)
Regards,
David Hager
Ogilvy, Thomas W., Mr., ODCSLOG wrote in message
<278EF0D03897D111880...@Pentagon-DADC020.army.mil>...
jupiter
"Rick Campbell" <ide...@pacbell.net> wrote:
>I'm using the following to extract the last name from a full name field:
>RIGHT(C2,SEARCH(" ",C2))
>It's supposed to check to the first space and return everything from there.
>But it ain't! The results have no consistency to them:
Yes, of course they have consistency, just not the consistency you
want! ;)
The problem is that you are searching from the left, but then picking
off characters from the right.
Try the following instead:
=RIGHT(C2,LEN(C2)-SEARCH(" ",C2))
The above will "check to the first space and return everything from
there." This returns the last name for all of your examples except
for the first one, where it returns:
& Silvia Aceves
For cases like this you will need a more sophisticated formula, or
some manual intervention.
>This is a sample of some original fields and the results:
>Original
>Carlos & Silvia Aceves
>Kelli Adams
>Josephine Adamson
>Ardath Albee
>Marilyn Alexander-Readers
>Les Alhadeff
>Results
> Aceves
> Adams
>ne Adamson
>h Albee
>-Readers
>deff
>Is my formula wrong? It works for LEFT (). Is there a bug in Excel? Help!!!
Laurent Longre
David,
This one should work in Excel 97 (and XL95, but not tested):
=TRIM(CALL("Crtdll","strrchr","CCI",A1,32))
Laurent ;-))
> >> I'm using the following to extract the last name from a full name
> >> field:
> >> RIGHT(C2,SEARCH(" ",C2))
> >>
> >> It's supposed to check to the first space and return everything from
> >> there.
> >> But it ain't! The results have no consistency to them:
> >>
> >> This is a sample of some original fields and the results:
> >> Original
> >> Carlos & Silvia Aceves
> >> Kelli Adams
> >> Josephine Adamson
> >> Ardath Albee
> >> Marilyn Alexander-Readers
> >> Les Alhadeff
> >>
> >> Results
> >> Aceves
> >> Adams
> >> ne Adamson
> >> h Albee
> >> -Readers
> >> deff
> >>
> >> Is my formula wrong? It works for LEFT (). Is there a bug in Excel?
> >> Help!!!
> >>
> >> --
Ogilvy, Thomas W., Mr., ODCSLOG
David,
Very straightforward. Thanks,
Tom Ogilvy
> ----------
> From: David Hager[SMTP:10407...@compuserve.com]
> Posted At: Thursday, May 28, 1998 9:58 PM
> Posted To: worksheetfunctions
> Conversation: RIGHT () not working
> Subject: Re: RIGHT () not working
henner juengst
"Rick Campbell" <ide...@pacbell.net> wrote:
>I'm using the following to extract the last name from a full name field:
>RIGHT(C2,SEARCH(" ",C2))
>
Hi Rick,
try: RIGHT(A1,(Length(A1)-SEARCH(" ",A1)))
--
HTH
Regards
Henner Juengst