Edit a column of names

[Martin ©¿©¬]

Hi
I have a worksheet with about 300 names in a column
The names are laid out like
Adrain Mr R. or
Buchanan Mr & Mrs T.
etc etc

Is there some way to remove the titles and just leave the Surname
without have to do it a line at-a-time?

Martin
©¿©¬

 

[Guest]

Martin,
In your worksheet, make sure you have blank columns to the right of the
column that contains the names.
Highlight the names.
Click the "Data" menu item.
Click "Text to Columns...".
In the pup-up wizard, select the type of data as "Delimited".
Click the "Next" button.
Remove the check mark from the "Tab" Delimiter.
Place a check mark in the "Other:" Delimiter.
Click in the box to the right of "Other:'
If your data list has spaces between the names and titles, press the space
bar one time to insert a space into this box.
Click the "Next" box.
In the "Data Preview" box, youwill see how Excel will parse your data.
If this is acceptable to you, press the "Finish" button. Wherever Excel
finds one space between the data in each cell, it will spread the data into
the cells to the right of your original list.
If Buchanan Mr & Mrs T. is in cell A1, then Buchanan will remain in cell A1
Mr will be moved to cell B1
& will be moved to C1, etc, and T. will be moved to cell E1.

Hope this helps.
Dave

 

[Gord Dibben]

Martin

Further to Dave's post.

You can choose to "skip" the columns you don't want to retain, like the
columns with Mr & Mrs T etc.

Keep just the column with the surname. Select the others and "skip".

The "skip" step is the last step before "Finish"

One more note.......you don't enter a <space>, just check the "Space" option
button. Ignore the "Other" checkbox.


Gord Dibben Excel MVP

 

[Martin ©¿©¬]

Many thanks Dave & Gord
That works great on a normal worksheet
However, what i forgot to mention in that my worksheet is LINKED to
14 other sheets and this doesn't seem to work with linked data

So is there a solution before i make an unlinked worksheet?

Regards
Martin
©¿©¬

 

[Gord Dibben]

Martin

Are you saying the names are the results of formulas that link to the other
sheets?

Can you just copy then Paste Special(in place)>Values>OK>Esc.


Gord Dibben Excel MVP

 

[Guest]

Martin,

I'm assuming that you have a formula such as vlookup or something that is
pulling the names together from the linked sheets, and what you are saying is
that you are unsuccessfully trying to parse the data in cells that contain
the formulas. If this is true, then:
Highlight the 300 cells that contain the names.
Copy the cells.
Open a new worksheet.
Click Edit, Paste Special... then Values
Perform the exercise from the previous reply to parse your data.
Copy and paste the parsed data back into your original worksheet or link the
data back to your original worksheet.