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
ｩｿｩｬ

[swatsp0p]

Insert a helper column next to your name column. Enter this formula in the top cell of the new col. and copy down to the end of your data range:

=LEFT(B1,FIND(" ",B1,1)-1)

This example assumes your name list begins in B1, adjust the references to B1 to meet your actual data location.

HTH

[DBavirsha]

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



"Martin ﾃつｩﾃつｿﾃつｩﾃつｬ @mandeREMOVETHIS.plus.com" wrote:

> 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
> ﾃつｩﾃつｿﾃつｩﾃつｬ
>
>
>

[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


On Wed, 2 Feb 2005 12:09:09 -0800, DBavirsha
<DBavirsha@discussions.microsoft.com> wrote:

>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
>
>
>
>"Martin ﾂｩﾂｿﾂｩﾂｬ @mandeREMOVETHIS.plus.com" wrote:
>
>> 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
>> ﾂｩﾂｿﾂｩﾂｬ
>>
>>
>>

[Martin &copy;&iquest;&copy;&not;]

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

On Thu, 03 Feb 2005 13:26:27 +0000, Martin ﾂｩﾂｿﾂｩﾂｬ @mandeREMOVETHIS.plus.com
wrote:

>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
>ﾂｩﾂｿﾂｩﾂｬ

[cycling-rod]

If you want to remove the titles completely you could use "Find and Replace All". This will work on linked sheets.

[DBavirsha]

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.

"Martin ﾃつｩﾃつｿﾃつｩﾃつｬ @mandeREMOVETHIS.plus.com" wrote:

> 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
> ﾃつｩﾃつｿﾃつｩﾃつｬ
>
>