Can I get words to switch places without having to do it by hand?.

[KrissMiss]

Can I get words to switch places without having to do it by hand? I have a
worksheet with 2300 names and addresses in rows. However, I need the name
column to read Last Name first. It doesn't. For clarification....It reads
John Doe & I need it to read Doe John. I inherited this worksheet & alot of
others like it & would prefer not to have to retype them all.
Thanks

 

[MartinW]

Hi Miss Cringle,

This will split everything at the first space so if any of
your names have a first and middle name you will need
a different approach.

With your names in A1 down, put this in B1 and drag down as far as needed.
=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",1)),100)&"
"&LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",1)))

Just in case that wraps i'll split it here.
=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",1)),100)
&" "&LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",1)))

Also, instead of dragging you can just double click on
the fill handle, provided there are no blanks in col. A

And another also, to clean it up afterwards, select
all of col B, copy it, then right click on the selection
and select Paste Special>Values.
After that you can delete column A

HTH
Martin

 

[MartinW]

Slight change, the other one left a trailing space, try this

=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",1)),100)
&" "&LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",1))-1)

And if you want a comma after the surname try this

=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",1)),100)
&", "&LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",1))-1)

HTH
Martin

 

[Dennis]

Assuming that your names are in column A and they are all firstname space
surname use the following in column B

=CONCATENATE(MID(A1,FIND(" ",A1,1)+1,LEN(A1)-FIND(" ",A1,1)),"
",LEFT(A1,FIND(" ",A1,1)-1))

You can then drag down this formula to repeat for all 2300 names.
You can then copy column B and paste special, values only into column A and
remove column B

 

[Robert McCurdy]

Before we start can you save your workbook?

Insert to the right of your names a sufficient number of blank columns (they
can be deleted afterwards).
Check to see - from Tools > Options > Calculation tab, the calculation
option is set to Automatic.

Select your names down to the last entry.
Run the Text to Columns wizard from your Data menu.
Pick Delimited, Next, and check Space and/or Tab, check to see if the words
are correctly spaced out and separated.
If so pick next and finish.

Now for the slightly complicated bit. If you have names with up to 4 names
they will be spread over four columns so use this formula in the 5th column.

=TRIM(A2&" "&B2&" "&C2&" "&D2)

If you have less leave out each extra &" "&D2 of the formula.

Select one column you wish to move and with the Shift key down use the mouse
click (hold down) and move it to your desired location. The other columns
will magically just move outa the way - when you let go. You should see the
insertion point as XL displays a slight grey line where the column is to be
inserted.
If you do this with the mouse 'Right Click', you will get a menu of options.
If you make a mistake just use Control Z to undo it.
Move any others likewise if required.

When the formula displays the names as you like it, select these formula
cells, Copy, from the Edit menu pick Paste Special, check the Values option,
OK. Press Escape key. Now you can remove those surplus columns safely.

This method is vastly more flexible and gives you the option to modify the
changes at any point, the formulas given make assumptions that may not work
in every situation you need them to. It seems like more work, it isn't!
GL


Regards
Robert McCurdy