formatting cells

  • Thread starter Thread starter sophie
  • Start date Start date
S

sophie

Hi
I am trying to fix up a spreadsheet that is accessed by a
lot of users so it is easier to read etc and am having
problems with the way one row has been set up. The row
contains first and surnames together in each cell in the
format: bob smith. However it has been set up so that the
cells are in alphabetical order by surname. So on first
glance, because the first name appears first, the list
looks random. I would like to change the cells so that
the surname appears first, this way the order of the names
won't change, is there a text function that will allow me
to do this? (I hope I have explained this clearly enough)
Thanks
Soph
 
Sophie, one way:

1-Insert two "helper" columns immediately to the right of the one with the
names.
2-Select the names and Data > Text to Columns. Delimited. Space character is
the delimiter.
3-Assuming first name is in A1 and last name is in B1, in C1 enter =B1&",
"&A1. Copy formula down.
4-Select names in Column C, and Edit > Copy > Edit > Paste Special > Values
OK.
Click to expand...
5-Delete Columns A and B.
 
Are there many double names, III Jr etc or some fancy Norman names?
If not you can use data>text to columns, delimited, space as delimiter, then
concatenate them. Assume that they are in A, make sure you insert a new
blank B (insert>column)
or else data in B will be overwritten, do data>text to columns, now you have
2 columns,
A with the first name and B with the last
In a third column use

=B2&" "A2

copy down, paste special as values in place. now manually do the names that
have more spaces than one

Or use a formula in a help column

=TRIM(MID(A1,FIND(" ",A1),255)&" "&TRIM(LEFT(A1,FIND(" ",A1))))

copy down.
 
Hi
I am trying to fix up a spreadsheet that is accessed by a
lot of users so it is easier to read etc and am having
problems with the way one row has been set up. The row
contains first and surnames together in each cell in the
format: bob smith. However it has been set up so that the
cells are in alphabetical order by surname. So on first
glance, because the first name appears first, the list
looks random. I would like to change the cells so that
the surname appears first, this way the order of the names
won't change, is there a text function that will allow me
to do this? (I hope I have explained this clearly enough)
Thanks
Soph
Click to expand...

Another way,

Assuming the names are in Column A and begin in A1, put the following
formula in B1 and copy down:

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

As in DDM's example, select names in Column B, Edit > Copy > Edit > Paste
Special > Values, and delete Column A.

Cheers!
 
Back
Top