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!

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

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

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!