I am not the greatest at programig this is what I have.

I recieved a data files with COLUMB A [ FISRT NAME] COLUMB B [LAST NAME]
now columb c is suppose to have just the address but it has the first and
last name in it as well. See below.

example:
COLUMB A | COLUMB B COLUMBC
-----------------------------------------------------------------------------------
John |Smith | John Smith 123 loveless drive Cville ST 12345

I would like to use COLUMB B "last name" find the string in COLUMB C and
trim from the charector count it finds from the end of the last name to the
begining of the cell. Can anyone help.
Thank you in advance.

Dan

Enter:


Sub gsnu()
Dim s As String
Dim r As Range
For Each r In Selection
s2 = Split(r.Value, " ")
u = UBound(s2)
s3 = s2(2)
For i = 3 To u
s3 = s3 & " " & s2(i)
Next
r.Value = s3
Next

End Sub

If you select cells in column C and run the macro it will remove the first
two words in the cell.
--
Gary''s Student


"DShaw" wrote:

I am not the greatest at programig this is what I have.

I recieved a data files with COLUMB A [ FISRT NAME] COLUMB B [LAST NAME]
now columb c is suppose to have just the address but it has the first and
last name in it as well. See below.

example:
COLUMB A | COLUMB B COLUMBC
-----------------------------------------------------------------------------------
John |Smith | John Smith 123 loveless drive Cville ST 12345

I would like to use COLUMB B "last name" find the string in COLUMB C and
trim from the charector count it finds from the end of the last name to the
begining of the cell. Can anyone help.
Thank you in advance.

Dan

Sub fixData()
Dim rng as Range, cell as Range
Dim f as Long, l as Long
set rng = Range(Cells(2,3),Cells(rows.count,3))
for each cell in rng
f = len(trim(Cell.offset(0,-2))
l = len(trim(cell.offset(0,-1))
cell.Value = Trim(Right(cell,len(cell)-(f + l + 2)))
Next
End Sub

Test it on a copy of your data

--
Regards,
Tom Ogilvy

"DShaw" wrote in message
...
I am not the greatest at programig this is what I have.

I recieved a data files with COLUMB A [ FISRT NAME] COLUMB B [LAST NAME]
now columb c is suppose to have just the address but it has the first and
last name in it as well. See below.

example:
COLUMB A | COLUMB B COLUMBC
-----------------------------------------------------------------------------------
John |Smith | John Smith 123 loveless drive Cville ST
12345

I would like to use COLUMB B "last name" find the string in COLUMB C and
trim from the charector count it finds from the end of the last name to
the
begining of the cell. Can anyone help.
Thank you in advance.

Dan

Thanks Gary but I should have mentioned that there are middle initials and
sometimes a & for two first names ie. Jake & Jackie L Peters
Thats why I was hoping to match Peters find the end charector count on
Peters and trim to the left to the begining of the cell.

Dan


"Gary''s Student" wrote:

Enter:


Sub gsnu()
Dim s As String
Dim r As Range
For Each r In Selection
s2 = Split(r.Value, " ")
u = UBound(s2)
s3 = s2(2)
For i = 3 To u
s3 = s3 & " " & s2(i)
Next
r.Value = s3
Next

End Sub

If you select cells in column C and run the macro it will remove the first
two words in the cell.
--
Gary''s Student


"DShaw" wrote:

I am not the greatest at programig this is what I have.

I recieved a data files with COLUMB A [ FISRT NAME] COLUMB B [LAST NAME]
now columb c is suppose to have just the address but it has the first and
last name in it as well. See below.

example:
COLUMB A | COLUMB B COLUMBC
-----------------------------------------------------------------------------------
John |Smith | John Smith 123 loveless drive Cville ST 12345

I would like to use COLUMB B "last name" find the string in COLUMB C and
trim from the charector count it finds from the end of the last name to the
begining of the cell. Can anyone help.
Thank you in advance.

Dan

Thank you Tom but I get a compilation error on the following 2 lines.

f = len(trim(Cell.offset(0,-2))
l = len(trim(cell.offset(0,-1))

and I'm not quite sure how to use the script.

"Tom Ogilvy" wrote:

Sub fixData()
Dim rng as Range, cell as Range
Dim f as Long, l as Long
set rng = Range(Cells(2,3),Cells(rows.count,3))
for each cell in rng
f = len(trim(Cell.offset(0,-2))
l = len(trim(cell.offset(0,-1))
cell.Value = Trim(Right(cell,len(cell)-(f + l + 2)))
Next
End Sub

Test it on a copy of your data

--
Regards,
Tom Ogilvy

"DShaw" wrote in message
...
I am not the greatest at programig this is what I have.

I recieved a data files with COLUMB A [ FISRT NAME] COLUMB B [LAST NAME]
now columb c is suppose to have just the address but it has the first and
last name in it as well. See below.

example:
COLUMB A | COLUMB B COLUMBC
-----------------------------------------------------------------------------------
John |Smith | John Smith 123 loveless drive Cville ST
12345

I would like to use COLUMB B "last name" find the string in COLUMB C and
trim from the charector count it finds from the end of the last name to
the
begining of the cell. Can anyone help.
Thank you in advance.

Dan

Just left off a right paren,

f = len(trim(Cell.offset(0,-2)))
l = len(trim(cell.offset(0,-1)))

Don't use it then. Since you posted in programming, assumed you wanted a
macro.

in any event, if the complete matching name is not shown in columns A and B,
no matter what the form, then the macro wouldn't work. That is the pattern
you described.



--
Regards,
Tom Ogilvy


"DShaw" wrote in message
...
Thank you Tom but I get a compilation error on the following 2 lines.

f = len(trim(Cell.offset(0,-2))
l = len(trim(cell.offset(0,-1))

and I'm not quite sure how to use the script.

"Tom Ogilvy" wrote:

Sub fixData()
Dim rng as Range, cell as Range
Dim f as Long, l as Long
set rng = Range(Cells(2,3),Cells(rows.count,3))
for each cell in rng
f = len(trim(Cell.offset(0,-2))
l = len(trim(cell.offset(0,-1))
cell.Value = Trim(Right(cell,len(cell)-(f + l + 2)))
Next
End Sub

Test it on a copy of your data

--
Regards,
Tom Ogilvy

"DShaw" wrote in message
...
I am not the greatest at programig this is what I have.

I recieved a data files with COLUMB A [ FISRT NAME] COLUMB B [LAST
NAME]
now columb c is suppose to have just the address but it has the first
and
last name in it as well. See below.

example:
COLUMB A | COLUMB B COLUMBC
-----------------------------------------------------------------------------------
John |Smith | John Smith 123 loveless drive Cville ST
12345

I would like to use COLUMB B "last name" find the string in COLUMB C
and
trim from the charector count it finds from the end of the last name to
the
begining of the cell. Can anyone help.
Thank you in advance.

Dan

I may not have explained myself very well so maybe we can take this from a
direction

First, Yes I did want it in VB macro.

The last name in COLUMB C is right before each address in the same cell
In COLUMB B exists the last name only. So can we match the last name in
COLUMB C with the last name in COLUMB B and the use split and write
everything to the right of the last name in COLUMB C to another cell to the
right?

I hope I am making myself clear.

Thanks for all you help.
Dan


"Tom Ogilvy" wrote:

Just left off a right paren,

f = len(trim(Cell.offset(0,-2)))
l = len(trim(cell.offset(0,-1)))

Don't use it then. Since you posted in programming, assumed you wanted a
macro.

in any event, if the complete matching name is not shown in columns A and B,
no matter what the form, then the macro wouldn't work. That is the pattern
you described.



--
Regards,
Tom Ogilvy


"DShaw" wrote in message
...
Thank you Tom but I get a compilation error on the following 2 lines.

f = len(trim(Cell.offset(0,-2))
l = len(trim(cell.offset(0,-1))

and I'm not quite sure how to use the script.

"Tom Ogilvy" wrote:

Sub fixData()
Dim rng as Range, cell as Range
Dim f as Long, l as Long
set rng = Range(Cells(2,3),Cells(rows.count,3))
for each cell in rng
f = len(trim(Cell.offset(0,-2))
l = len(trim(cell.offset(0,-1))
cell.Value = Trim(Right(cell,len(cell)-(f + l + 2)))
Next
End Sub

Test it on a copy of your data

--
Regards,
Tom Ogilvy

"DShaw" wrote in message
...
I am not the greatest at programig this is what I have.

I recieved a data files with COLUMB A [ FISRT NAME] COLUMB B [LAST
NAME]
now columb c is suppose to have just the address but it has the first
and
last name in it as well. See below.

example:
COLUMB A | COLUMB B COLUMBC
-----------------------------------------------------------------------------------
John |Smith | John Smith 123 loveless drive Cville ST
12345

I would like to use COLUMB B "last name" find the string in COLUMB C
and
trim from the charector count it finds from the end of the last name to
the
begining of the cell. Can anyone help.
Thank you in advance.

Dan

Select the data in column C
then run the macro

Sub SplitData()
For Each cell In Selection
v = Split(cell.Value, cell.Offset(0, -1).Value)
cell.Value = Trim(v(1))
Next
End Sub

--
Regards,
Tom Ogilvy


--
regards,
Tom Ogilvy


"DShaw" wrote:

I may not have explained myself very well so maybe we can take this from a
direction

First, Yes I did want it in VB macro.

The last name in COLUMB C is right before each address in the same cell
In COLUMB B exists the last name only. So can we match the last name in
COLUMB C with the last name in COLUMB B and the use split and write
everything to the right of the last name in COLUMB C to another cell to the
right?

I hope I am making myself clear.

Thanks for all you help.
Dan


"Tom Ogilvy" wrote:

Just left off a right paren,

f = len(trim(Cell.offset(0,-2)))
l = len(trim(cell.offset(0,-1)))

Don't use it then. Since you posted in programming, assumed you wanted a
macro.

in any event, if the complete matching name is not shown in columns A and B,
no matter what the form, then the macro wouldn't work. That is the pattern
you described.



--
Regards,
Tom Ogilvy


"DShaw" wrote in message
...
Thank you Tom but I get a compilation error on the following 2 lines.

f = len(trim(Cell.offset(0,-2))
l = len(trim(cell.offset(0,-1))

and I'm not quite sure how to use the script.

"Tom Ogilvy" wrote:

Sub fixData()
Dim rng as Range, cell as Range
Dim f as Long, l as Long
set rng = Range(Cells(2,3),Cells(rows.count,3))
for each cell in rng
f = len(trim(Cell.offset(0,-2))
l = len(trim(cell.offset(0,-1))
cell.Value = Trim(Right(cell,len(cell)-(f + l + 2)))
Next
End Sub

Test it on a copy of your data

--
Regards,
Tom Ogilvy

"DShaw" wrote in message
...
I am not the greatest at programig this is what I have.

I recieved a data files with COLUMB A [ FISRT NAME] COLUMB B [LAST
NAME]
now columb c is suppose to have just the address but it has the first
and
last name in it as well. See below.

example:
COLUMB A | COLUMB B COLUMBC
-----------------------------------------------------------------------------------
John |Smith | John Smith 123 loveless drive Cville ST
12345

I would like to use COLUMB B "last name" find the string in COLUMB C
and
trim from the charector count it finds from the end of the last name to
the
begining of the cell. Can anyone help.
Thank you in advance.

Dan