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

Hi Jeff,
See http://www.mvps.org/dmcritchie/excel/join.htm#septerm
make sure that the column to the right is empty and the words
after the first word will be moved to the next column.
If you have names like "van Buren" then change all "van " to "van$"
and change the $ signs back to spaces afterwards.
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"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

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

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

Thank you very much. Looks complicated (I am an Excel newbie) but I
will study it and try it.

Jeff

David McRitchie wrote:
Hi Jeff,
See http://www.mvps.org/dmcritchie/excel/join.htm#septerm
make sure that the column to the right is empty and the words
after the first word will be moved to the next column.
If you have names like "van Buren" then change all "van " to
"van$" and change the $ signs back to spaces afterwards.

"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

Tried it and it works just fine. Thank you very much.

Jeff

David McRitchie wrote:
Hi Jeff,
See http://www.mvps.org/dmcritchie/excel/join.htm#septerm
make sure that the column to the right is empty and the words
after the first word will be moved to the next column.
If you have names like "van Buren" then change all "van " to
"van$" and change the $ signs back to spaces afterwards.

"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

Is there a way to add a macro to a toolbar in Excel the way I can in
Word? I cannot seem to be able to find the macro I just created to make
it a button on the toolbar. I can execute it from the Ctrl-t shortcut
but a button would be nice.

Thank you.

Jeff

David McRitchie wrote:
Hi Jeff,
See http://www.mvps.org/dmcritchie/excel/join.htm#septerm
make sure that the column to the right is empty and the words
after the first word will be moved to the next column.
If you have names like "van Buren" then change all "van " to
"van$" and change the $ signs back to spaces afterwards.

"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

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

Thank you.

For David's module I went about it in a circuitous way because I did not
know how to create a macro. I first created a fake macro by recording
"something". I then went to Macros and click on edit. Instead of
editing it I deleted its contents and inserted David's and it worked.
(Because I did not know how to create one from scratch I had to
improvise. Excel's help was not helpful here).

Jeff

Steve Yandl wrote:
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

For future reference, you can press Alt plus F8, type in the name for your
new subroutine and then press the 'Create' button. An alternate is to press
Alt plus F11 to go directly to the Visual Basic for Applications Editor but
then a new module isn't automatically created for you as the storage place
for new subroutines or user defined functions.

Once you have created a macro, Alt plus F11 is the way to go back and edit
that subroutine or add new subs.

Steve



"Jeff" wrote in message
...
Thank you.

For David's module I went about it in a circuitous way because I did not
know how to create a macro. I first created a fake macro by recording
"something". I then went to Macros and click on edit. Instead of editing
it I deleted its contents and inserted David's and it worked. (Because I
did not know how to create one from scratch I had to improvise. Excel's
help was not helpful here).

Jeff

Steve Yandl wrote:
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

Thank you very much.

Jeff

Steve Yandl wrote:
For future reference, you can press Alt plus F8, type in the name for
your new subroutine and then press the 'Create' button. An alternate
is to press Alt plus F11 to go directly to the Visual Basic for
Applications Editor but then a new module isn't automatically created
for you as the storage place for new subroutines or user defined
functions.
Once you have created a macro, Alt plus F11 is the way to go back and
edit that subroutine or add new subs.

Steve



"Jeff" wrote in message
...
Thank you.

For David's module I went about it in a circuitous way because I did
not know how to create a macro. I first created a fake macro by
recording "something". I then went to Macros and click on edit.
Instead of editing it I deleted its contents and inserted David's
and it worked. (Because I did not know how to create one from
scratch I had to improvise. Excel's help was not helpful here).

Jeff

Steve Yandl wrote:
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

Since you mentioned macro in the subject, I thought you
were set on how to install and use; otherwise, I would
have include link to
http://www.mvps.org/dmcritchie/excel....htm#havemacro
But you still may find point of interest in the entire page.

Incidentally my macro does require you to insert an empty
column to the right of the selected column as the first
word remains in the selected column and the next column
to the right receives the rest of the data.

The Join macro on the join.htm page can be used to
combine selected columns back to a single column for
the reverse of what you asked.
--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Jeff" wrote in message ...
Thank you very much.

Jeff

Steve Yandl wrote:
For future reference, you can press Alt plus F8, type in the name for
your new subroutine and then press the 'Create' button. An alternate
is to press Alt plus F11 to go directly to the Visual Basic for
Applications Editor but then a new module isn't automatically created
for you as the storage place for new subroutines or user defined
functions.
Once you have created a macro, Alt plus F11 is the way to go back and
edit that subroutine or add new subs.

Steve



"Jeff" wrote in message
...
Thank you.

For David's module I went about it in a circuitous way because I did
not know how to create a macro. I first created a fake macro by
recording "something". I then went to Macros and click on edit.
Instead of editing it I deleted its contents and inserted David's
and it worked. (Because I did not know how to create one from
scratch I had to improvise. Excel's help was not helpful here).

Jeff

Steve Yandl wrote:
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

Thank you.

Jeff

David McRitchie wrote:
Since you mentioned macro in the subject, I thought you
were set on how to install and use; otherwise, I would
have include link to
http://www.mvps.org/dmcritchie/excel....htm#havemacro
But you still may find point of interest in the entire page.

Incidentally my macro does require you to insert an empty
column to the right of the selected column as the first
word remains in the selected column and the next column
to the right receives the rest of the data.

The Join macro on the join.htm page can be used to
combine selected columns back to a single column for
the reverse of what you asked.

"Jeff" wrote in message
...
Thank you very much.

Jeff

Steve Yandl wrote:
For future reference, you can press Alt plus F8, type in the name
for your new subroutine and then press the 'Create' button. An
alternate is to press Alt plus F11 to go directly to the Visual
Basic for Applications Editor but then a new module isn't
automatically created for you as the storage place for new
subroutines or user defined functions.
Once you have created a macro, Alt plus F11 is the way to go back
and edit that subroutine or add new subs.

Steve



"Jeff" wrote in message
...
Thank you.

For David's module I went about it in a circuitous way because I
did not know how to create a macro. I first created a fake macro
by recording "something". I then went to Macros and click on edit.
Instead of editing it I deleted its contents and inserted David's
and it worked. (Because I did not know how to create one from
scratch I had to improvise. Excel's help was not helpful here).

Jeff

Steve Yandl wrote:
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