Extract and Reverse Text

[DDH]

I am trying to extract text and reverse it. Here is a example.

Located in Cell A1

ABC Company-Smith, Joe

I am trying to have in Cell D1

Joe Smith

The line in cell A1 will always have the " - " between company and the
name and with that I can
seperate the text and I can get it to reverse the name. I can not get
it to do it togeather.
The Name will always have a " , " between the last and first name.
The name could have a middle name.
I don't know if this can be done.

Thank you very much for your help.

 

[Bob Phillips]

This will work in the specific instance you give

=MID(A1,FIND(",",A1,FIND("-",A1)+1)+2,255)&"
"&MID(A1,FIND("-",A1)+1,FIND(",",A1)+1-FIND("-",A1)-1)

It will also work for
Smith, Billy Joe,
Smith, Dr Bill
de Smith, Joe

but I venture there are other instances where it wouldn't.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi DDH!

Try:
=RIGHT(A1,LEN(A1)-FIND(",",A1)-1)&"
"&MID(A1,FIND("-",A1)+1,FIND(",",A1)-FIND("-",A1)-1)

Seems to work OK with or without a middle name
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi

in addition to Bob's answer take a look at
http://www.cpearson.com/excel/FirstLast.htm

Those formulas will do some more error checking (e.g. only lastname
without firstname, etc.)

HTH
Frank

 

[Norman Harker]

Hi Bob!

Re: "but I venture there are other instances where it wouldn't."

It's called Murphy's Law.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[DDH]

Thank you very much for the formulas. I could not put them togeather.
I could do them own their own but not togaether.
I tried each of the formulas and they have the following results and
don't understand what I done wrong
Both of them do not have a space seperating the first and last name an
one of them still has the " , ".
Thank you very much for all of your help and knowledge.




ABC Company-Smith, Joe JoeSmith

ABC Company-Smith,Joe JoeSmith

 

[DDH]

I just figured out where to put the space at.
Thank you so very much for your help and all of your knowledge and
expert advise.



RIGHT(C4139,LEN(C4139)-FIND(",",C4139)-1)&"
"&MID(C4139,FIND("-",C4139)+1,FIND(",",C4139)-FIND("-",C4139)-1)

 

[Frank Kabel]

Hi

first of all you have to skip the company name (e.g. with
=RIGHT(A1,LEN(A1)-FIND("-",A1)). On this result you can use Chip's
formulas

HTH
Frank

 

[Bob Phillips]

Mine has the trailing , I thought I got rid of, but has a space in-between
in my tests. This should get rid of the ,

=MID(A1,FIND(",",A1,FIND("-",A1)+1)+2,255)&"
"&MID(A1,FIND("-",A1)+1,FIND(",",A1)+1-FIND("-",A1)-2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[DDH]

Thank you for all of your help. I have another question that I wondere
if it is possible. I have been working on it for two days and I am no
smart enough figure it out.
Here is the basic cell line, Extract and Reverse.

(Cell C2) ABC Company-Smith, Joe
The formula is in (D2) and the result would be Joe Smith

The foumula below works if there is no other " - " or " / " or anythin
else in the cell.
I have tried all sort of things to always make it pick up the very las
" - " and disreguard all other characters and I don't know if it i
possible.
Thank you for your help and knowledge.


RIGHT(C2,LEN(C2)-FIND(",",C2)-1)&
"&MID(C2,FIND("-",C2)+1,FIND(",",C2)-FIND("-",C2)-1

 

[Dave Peterson]

I'd use a helper cell to pick out the name, then reverse that in another cell:

From a formula by Peo Sjoblom (IIRC).

=MID(C2,FIND(CHAR(1),SUBSTITUTE(C2,"-",CHAR(1),
LEN(C2)-LEN(SUBSTITUTE(C2,"-",""))),1)+1,200)

(all one cell)
This will return "Smith, Joe").

then use this in another cell:
=TRIM(MID(D2,FIND(",",D2)+1,200))&" "&TRIM(LEFT(D2,FIND(",",D2)-1))
to reverse it to "Joe Smith"

(I used D2 as my helper cell.

 

[Harlan Grove]

I'd use a helper cell to pick out the name, then reverse that in another cell:

From a formula by Peo Sjoblom (IIRC).

=MID(C2,FIND(CHAR(1),SUBSTITUTE(C2,"-",CHAR(1),
LEN(C2)-LEN(SUBSTITUTE(C2,"-",""))),1)+1,200)

(all one cell)
This will return "Smith, Joe").

then use this in another cell:
=TRIM(MID(D2,FIND(",",D2)+1,200))&" "&TRIM(LEFT(D2,FIND(",",D2)-1))
to reverse it to "Joe Smith"

Hyphenated names would be a problem, e.g., John-Paul Price-Jones.

If the first character of the last name always *immediately* follows the
separating hyphen with no intervening space characters, and if there were never
more than two parts to hyphenated surnames, then regular expressions would make
short work of this. With the Subst() udf in

http://www.google.com/[email protected]

the pathological record

Foo Company-Incorporated-Price-Jones, John-Paul

can be parsed with

=Subst(A1,".+?\S-((['A-Za-z]+-)?['A-Za-z]+) *, *([- A-Za-z]+)$","$3 $1")

which returns

John-Paul Price-Jones

Obviously the challenge is to try to do this with only built-in functions and as
few ancillary cells as possible. Good luck.

 

[Tushar Mehta]

I'm not sure if you have a resolution, but if not...

If you install the code at http://www.tmehta.com/regexp/add_code.htm

you can then use the regular expression capability to get
=RegExpSubstitute(A2,"[^-]+-([^,]+), *(.+)$","$2 $1")

This identifies any non-hyphen character occuring one or more times
[^-]+ followed by a hyphen character - followed by, and save this, any
non-comma characters occuring one or more times ([^,]+) followed by a
comma , followed by any number of optional spaces * followed by, and
save this, one or more of any character (.+) to the end of the line $

The replacement token is the 2nd string of saved characters followed by
a space followed by the 1st string of saved characters.

Since a person's name can have a hyphen in it, the formula above
assumes that no company name can. Otherwise, there is no way to
distinguish between a company name and a person's name, e.g. Miriam-
Webster-Peters, Mary

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[DDH]

It now works perfect.

Thank you all very much

for all of your help and knowledge on this problem and in the past.
You are very nice group of people to share your knowledge. I admire all
of you very much.