Reverse Text in Cell before and after comma (not just Word1, Word2
 

I've seen some workarounds for doing

Smith, John

to

John Smith

But I am looking for

Word1 MaybeWord2-3, WordA MaybeWordB-C

i.e. just put all the words after the comma in front and vice versa e.g.

Assistant Manager, Building Services=Building Services Assistant Manager
Manager, Building Services = Building Services Manager
Assistant Manager, Building = Building Assistant Manager

Is there an 'easy' way to do this with cell functions vs VBA?

Thanks in advance!

Reverse Text in Cell before and after comma (not just Word1, Word2
 

I sometimes break this into smaller pieces.

Say your data is in column A (A1:A3)

I'd put this formula in B1:
=SEARCH(",",A1)
This returns the location of the first comma in A1.

Then since I want everything before that first comma, I'd use a formula like
this in C1:
=TRIM(LEFT(A1,B1-1))
I added trim just in case there were leading/trailing spaces.

Then for the stuff after the initial comma, I'd use a formula like this in D1:
=TRIM(MID(A1,B1+1,255))
255 is just a number big enough for the longest string that you have. Make it
bigger than what you need and it'll be ok.

Then I just have to concatenate the strings in C1 and D1 (well, D1 and C1) with
a formula like this in E1:
=D1&", "&C1

Then I drag all the formulas down as far as I need.

I'll convert the formulas in E1 to values (edit|copy, edit|paste
special|values).

And delete the other columns (B:D and maybe A???)

You can do it in a single formula if you want:
=TRIM(MID(A1,SEARCH(",",A1)+1,255))&", "&TRIM(LEFT(A1,SEARCH(",",A1)-1))

Personally, it doesn't bother me if I have to use intermediate cells--especially
if I'm gonna delete them before I release the workbook to others.

msnyc07 wrote:

I've seen some workarounds for doing

Smith, John

to

John Smith

But I am looking for

Word1 MaybeWord2-3, WordA MaybeWordB-C

i.e. just put all the words after the comma in front and vice versa e.g.

Assistant Manager, Building Services=Building Services Assistant Manager
Manager, Building Services = Building Services Manager
Assistant Manager, Building = Building Assistant Manager

Is there an 'easy' way to do this with cell functions vs VBA?

Thanks in advance!

--

Dave Peterson

Reverse Text in Cell before and after comma (not just Word1, Word2
 

On Thu, 24 Dec 2009 15:45:01 -0800, msnyc07
wrote:

I've seen some workarounds for doing

Smith, John

to

John Smith

But I am looking for

Word1 MaybeWord2-3, WordA MaybeWordB-C

i.e. just put all the words after the comma in front and vice versa e.g.

Assistant Manager, Building Services=Building Services Assistant Manager
Manager, Building Services = Building Services Manager
Assistant Manager, Building = Building Assistant Manager

Is there an 'easy' way to do this with cell functions vs VBA?

Thanks in advance!


=TRIM(MID(A1,FIND(",",A1)+1,99) & " " & LEFT(A1,FIND(",",A1)-1))

--ron

Reverse Text in Cell before and after comma (not just Word1, Word2
 

Assuming no leading or trailing spaces and always a comma/space separator...

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

--
Rick (MVP - Excel)


"msnyc07" wrote in message
...
I've seen some workarounds for doing

Smith, John

to

John Smith

But I am looking for

Word1 MaybeWord2-3, WordA MaybeWordB-C

i.e. just put all the words after the comma in front and vice versa e.g.

Assistant Manager, Building Services=Building Services Assistant Manager
Manager, Building Services = Building Services Manager
Assistant Manager, Building = Building Assistant Manager

Is there an 'easy' way to do this with cell functions vs VBA?

Thanks in advance!