Excel - Parse Name

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 16 Mar 2008 23:35 GMT

I have to parse out names that are not consistent. The data comes in as:
Mary Smith
John J Smith

I want the out to be: Smith John J or Smith Mary. Can anyone help?

Signature

Thanks!
Chris

Reply to this Message

Gary''s Student - 17 Mar 2008 00:08 GMT

Easy if we use two cells. For any name (or phrase) assume a set of words
separated by a single space. First find the location of the LAST space in
the phrase. Then everything to the right of the last space goes first and
everything to the left of the last space follows.

In A1:

James L Ravenswood

In B1:

=SEARCH("|",SUBSTITUTE(A1," ","|",(LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

In C1:

=RIGHT(A1,LEN(A1)-B1) & " " & LEFT(A1,B1-1)

So B1 shows: 8
and C1 shows: Ravenswood James L

Signature

Gary''s Student - gsnu200773

> I have to parse out names that are not consistent. The data comes in as:
> Mary Smith
> John J Smith
>
> I want the out to be: Smith John J or Smith Mary. Can anyone help?

Reply to this Message

Ron Rosenfeld - 17 Mar 2008 00:52 GMT

>I have to parse out names that are not consistent. The data comes in as:
>Mary Smith
>John J Smith
>
>I want the out to be: Smith John J or Smith Mary. Can anyone help?

=MID(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(TRIM(A1))," ",CHAR(1),
LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))+1,255)&" "&
LEFT(TRIM(TRIM(A1)),FIND(CHAR(1),SUBSTITUTE(TRIM(TRIM(A1))," ",CHAR(1),
LEN(TRIM(TRIM(A1)))-LEN(SUBSTITUTE(TRIM(TRIM(A1))," ",""))))-1)

would do that.
--ron

Reply to this Message

Chris - 17 Mar 2008 18:49 GMT

Thanks!

Signature

Thanks!
Chris

> >I have to parse out names that are not consistent. The data comes in as:
> >Mary Smith
[quoted text clipped - 9 lines]
> would do that.
> --ron

Reply to this Message

Teethless mama - 17 Mar 2008 04:05 GMT

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&"
"&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1)

> I have to parse out names that are not consistent. The data comes in as:
> Mary Smith
> John J Smith
>
> I want the out to be: Smith John J or Smith Mary. Can anyone help?

Reply to this Message

Chris - 17 Mar 2008 18:48 GMT

Thanks!

Signature

Thanks!
Chris

> =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))&"
> "&LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))-1)
[quoted text clipped - 4 lines]
> >
> > I want the out to be: Smith John J or Smith Mary. Can anyone help?

Reply to this Message

Chris - 17 Mar 2008 18:46 GMT

Gary's Student: Thanks! This worked great!!!

Signature

Thanks!
Chris

> I have to parse out names that are not consistent. The data comes in as:
> Mary Smith
> John J Smith
>
> I want the out to be: Smith John J or Smith Mary. Can anyone help?

Reply to this Message