Excel Query

[spotit3]

I am using a list of names using Excel. Each name is in one column e.g. Mr & Mrs John Smith. I need to separate the surnames into a separate column to enable me to sort them into alphabetical order. I can do it one at a time by using Data - Text to Columns but cannot figure out how to then copy this down the whole sheet without having to go over each one individually. I know I did it last year but just cannot remember how. I thought it was just a matter of pulling them down as I do when using formulae but that doesn't work. Any help would be much appreciated as there are aroung 400 names and addresses.

[NazNomad]

Not sure of the 'proper' way to do this, but I've had to order number by the last 3 digits before. I exported the sheet to a .txt file and inserted a comma as a delimiter, then imported it back into Excel.

Basically, making it 2 columns.

[spotit3]

Thanks NazNomad but I can separate the surnames OK. Just can't figure out how to copy this down the entire column of names without having to use Data-Text individually for each name.

[heathfield]

Select the column with the surnames. Click on 'Data'. Click on 'Sort'. You should get a dialogue box saying that Excel has found more data in adjacent columns and what do you want to do about it it? Choose 'Expand the selection'. Other columns will then be included, and a new dialogue box appears Clicking on the drop-down arrow will produce another box with your option of choosing a column. Select the column you want to sort by, (the one with the surnames in your case), and click OK.

[bushbaby_de]

click on the letter on the column header to highlight the whole column, then do data...text as normal?

[heathfield]

Oops, looks like I was looking up the wrong tree with what you wanted to do. Doh!!

[scotman]

Spotit

Are you selecting the column separators using the vertical lines in Excel. If yes I don't think you can copy them as they operate as fixed width and will not match in other cells.
You need to adjust the data as per Naz's suggestion before importing.

[spotit3]

Sorry to appear stupid but how do I export the sheet to a txt file?

[factor-fiction]

I've just set up an example on Excel. Just copy and paste the column into Word then do a text/table convversion specifying a space as giving a new column.
Then paste the 2 columns back into Excel

[spotit3]

Thanks to everyone for their answers.