The subroutine ("macro") would be placed in a module, the same as you did
with the macro created by David.

The first word and the space that follows it would be removed.

The changes are made in the original cell and it doesn't matter what is in
the adjacent columns.


Steve



"Jeff" wrote in message
...
Daer Steve

First thank you very very much. I was starting to think there was no real
way to do this.

Now the questions because I am far from an expert in Excel:
1. What do I do with this code? How do I make it into a macro I can use?
Where do I enter it?
2. When you say this "will remove the first word prior to the space", will
it also remove that space that follows that first word?
3. Does it do its changes in the same field and column or do I need to add
an empty column to the right for it to copy the results to?

Again, thank you very much.

Jeff


Steve Yandl wrote:
Jeff,

Here is a slightly different approach than David's. The sub between
the lines will remove the first word prior to the space unless there
is only a single name and then it leaves the cell alone (could be
altered). For the example, I have it just handling column "B:B" but
that too can be changed. If you have a header row, it will work on
that as well so that might need to be changed.

_________________________________

Sub CleanColumnB()
Dim rngX As Range
Dim strName As String
Set rngX = Intersect(Columns("B:B"), ActiveSheet.UsedRange)
For Each myCell In rngX
strName = myCell.Text
txtArray = Split(strName, " ")
If UBound(txtArray) 0 Then
strName = ""
For T = 1 To UBound(txtArray)
strName = strName & " " & txtArray(T)
Next T
myCell.Value = LTrim(strName)
End If
Next myCell
End Sub

________________________________

Steve



"Jeff" wrote in message
...
Hi

I'm working in 2002 and have a spreadsheet with several columns. One of
the columns has text consisting of a surname and several
other names after each entry. That is each field (or line) in that
column contains several words. I need to delete the first word
(happens to be the surname) in each field in that column. Because a
dealing with several thousand records, I would obviously like to
create a macro to do this, one line at a time. I attempted to
create this macro with the "record a macro" function. But it has
not worked for me. The macro I produced just pastes the same end
result (from the first field) in every field I run it. Example:
Changing "Smith John Andrew" to "John Andrew"
Changing "Henderson Frank: to "Frank"
etc.

Basically, once I am in a field in that column, I want the macro to
do its thing, I would like it to:

F2 (to edit the field)
Home (to go to the beginning of the text words)
Shift-Ctrl-Right arrow (to select the first word)
Delete (to delete that selected first word)
Enter (to accept the result ad move to the next field below it).

I have been unsuccessful in creating that macro - or at least it
does not work correctly when recorded and just pastes the results of
the first field I created the macro in. Is it possible to create
such a macro? I do not know Visual basic and therefore cannot write
it from scratch, assuming it was possible to do so.

Can someone help? Any help would be greatly appreciated.

Jeff