Board logo

Excel guru needed .....
Jasper - 14/2/06 at 02:04 PM

I have an address list with the first column with the full name in it, but last name first. I want to swap the names around in the first column so I can print out address labels, and I know there's an easy way to do if but I'm bugger if I can find or remember it.

Any advice gratefully received ...

Agriv8 - 14/2/06 at 02:16 PM

Quickest way :-

1 insert blank column to the Right
2 highlight Column
3 menu data --> text to columns
4 select delimited data
5 take a tick out of tab and tick 'other' and enter a space in box
6 check this puts a line in the right Place
7 click finish

Should have data accross two columns mail merge as normal

gingerprince - 14/2/06 at 02:17 PM

quote:
Originally posted by Jasper
I have an address list with the first column with the full name in it, but last name first. I want to swap the names around in the first column so I can print out address labels, and I know there's an easy way to do if but I'm bugger if I can find or remember it.

Any advice gratefully received ...


Highight the column, then choose "Text to columns" from the Data menu. Split by "space" delimiter then this will split them into columns. You can then move the surname to the last column, or use the CONCATENATE function to reform into the correct order.


Sy

nludkin - 14/2/06 at 02:22 PM

Or the old fashioned way.

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

Where A1 is the cell that the name is in.

nludkin - 14/2/06 at 02:29 PM

Doh. Schoolboy error.. Fixed :-)

=CONCATENATE(TRIM(RIGHT(A1,LEN(A1)-FIND(",",A1,1))), " ", LEFT(A1, FIND(",", A1,1)-1))

I hate excel

Jasper - 14/2/06 at 02:36 PM

Cheers chaps