Matching CELL text (full word) to full word in string in another

[DShaw]

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

 

[Gary''s Student]

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.

 

[Tom Ogilvy]

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

 

[DShaw]

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

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

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

 

[DShaw]

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.

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

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

 

[Tom Ogilvy]

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

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.

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

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

 

[DShaw]

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

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

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.

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

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

 

[Tom Ogilvy]

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

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

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

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.

:

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

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