Hi Everyone

I have a speadsheet with columns of credit card
transactions.
Column B contains the name & address of the retailers as
show below:

FOODLAND PARKLAND PAR
SAMS SEAFOOD RICHMOND PAR
SHELL SELF SERVE ELIZABETH VLE
DICK SMITH W8080 ELIZABETH CIT

How do I Find and Replace all instances of PAR on the
right hand end of cell with PARK without changing
PARKLAND to PARKKLAND with two "K's" ?.

TIA

Bob C

Bob,

Replace PARK with a placeholder, do your replacement, and restore the
PARKs. This will work as long as XXXX doesn't appear anywhere in your
data set.

With Columns("B:B")
..Replace What:="PARK", Replacement:="XXXX", LookAt:=xlPart
..Replace What:="PAR", Replacement:="PARK", LookAt:=xlPart
..Replace What:="XXXX", Replacement:="PARK", LookAt:=xlPart
End With

HTH,
Bernie
MS Excel MVP

"Bob C" wrote in message
...
Hi Everyone

I have a speadsheet with columns of credit card
transactions.
Column B contains the name & address of the retailers as
show below:

FOODLAND PARKLAND PAR
SAMS SEAFOOD RICHMOND PAR
SHELL SELF SERVE ELIZABETH VLE
DICK SMITH W8080 ELIZABETH CIT

How do I Find and Replace all instances of PAR on the
right hand end of cell with PARK without changing
PARKLAND to PARKKLAND with two "K's" ?.

TIA

Bob C

I doubt that you can. Replace doesn't offer that level of sophistication to
the best of my knowledge.

You would have to write a macro with a specific algorithm to identify this
situation and make the change.

--
Regards,
Tom Ogilvy

"Bob C" wrote in message
...
Hi Everyone

I have a speadsheet with columns of credit card
transactions.
Column B contains the name & address of the retailers as
show below:

FOODLAND PARKLAND PAR
SAMS SEAFOOD RICHMOND PAR
SHELL SELF SERVE ELIZABETH VLE
DICK SMITH W8080 ELIZABETH CIT

How do I Find and Replace all instances of PAR on the
right hand end of cell with PARK without changing
PARKLAND to PARKKLAND with two "K's" ?.

TIA

Bob C

Hi Tom
I have just noticed in changing my question around, I
have deleted the part about doing this via macro.
Sorry and thankyou

Bob C

-----Original Message-----
I doubt that you can. Replace doesn't offer that level
of sophistication to
the best of my knowledge.

You would have to write a macro with a specific
algorithm to identify this
situation and make the change.

--
Regards,
Tom Ogilvy

"Bob C" wrote in
message
...
Hi Everyone

I have a speadsheet with columns of credit card
transactions.
Column B contains the name & address of the retailers
as
show below:

FOODLAND PARKLAND PAR
SAMS SEAFOOD RICHMOND PAR
SHELL SELF SERVE ELIZABETH VLE
DICK SMITH W8080 ELIZABETH CIT

How do I Find and Replace all instances of PAR on the
right hand end of cell with PARK without changing
PARKLAND to PARKKLAND with two "K's" ?.

TIA

Bob C


.

Bob,

In case my post has been overlooked or lost:

Bob,

Replace PARK with a placeholder, do your replacement, and restore the
PARKs. This will work as long as XXXX doesn't appear anywhere in your
data set.

With Columns("B:B")
..Replace What:="PARK", Replacement:="XXXX", LookAt:=xlPart
..Replace What:="PAR", Replacement:="PARK", LookAt:=xlPart
..Replace What:="XXXX", Replacement:="PARK", LookAt:=xlPart
End With

HTH,
Bernie
MS Excel MVP


"Bob C" wrote in message
...
Hi Tom
I have just noticed in changing my question around, I
have deleted the part about doing this via macro.
Sorry and thankyou

Bob C

-----Original Message-----
I doubt that you can. Replace doesn't offer that level
of sophistication to
the best of my knowledge.

You would have to write a macro with a specific
algorithm to identify this
situation and make the change.

--
Regards,
Tom Ogilvy

"Bob C" wrote in
message
...
Hi Everyone

I have a speadsheet with columns of credit card
transactions.
Column B contains the name & address of the retailers
as
show below:

FOODLAND PARKLAND PAR
SAMS SEAFOOD RICHMOND PAR
SHELL SELF SERVE ELIZABETH VLE
DICK SMITH W8080 ELIZABETH CIT

How do I Find and Replace all instances of PAR on the
right hand end of cell with PARK without changing
PARKLAND to PARKKLAND with two "K's" ?.

TIA

Bob C


.

Hi Bernie
So that's what happens after you read "can not find ISP"
I did post a thankyou and stated your code worked just
fine, but it got lost in transit.

Thankyou
Bob C

-----Original Message-----
Bob,

In case my post has been overlooked or lost:

Bob,

Replace PARK with a placeholder, do your replacement,
and restore the
PARKs. This will work as long as XXXX doesn't appear
anywhere in your
data set.

With Columns("B:B")
..Replace What:="PARK", Replacement:="XXXX",
LookAt:=xlPart
..Replace What:="PAR", Replacement:="PARK",
LookAt:=xlPart
..Replace What:="XXXX", Replacement:="PARK",
LookAt:=xlPart
End With

HTH,
Bernie
MS Excel MVP


"Bob C" wrote in
message
...
Hi Tom
I have just noticed in changing my question around, I
have deleted the part about doing this via macro.
Sorry and thankyou

Bob C

-----Original Message-----
I doubt that you can. Replace doesn't offer that
level
of sophistication to
the best of my knowledge.

You would have to write a macro with a specific
algorithm to identify this
situation and make the change.

--
Regards,
Tom Ogilvy

"Bob C" wrote in
message
...
Hi Everyone

I have a speadsheet with columns of credit card
transactions.
Column B contains the name & address of the
retailers
as
show below:

FOODLAND PARKLAND PAR
SAMS SEAFOOD RICHMOND PAR
SHELL SELF SERVE ELIZABETH VLE
DICK SMITH W8080 ELIZABETH CIT

How do I Find and Replace all instances of PAR on
the
right hand end of cell with PARK without changing
PARKLAND to PARKKLAND with two "K's" ?.

TIA

Bob C


.



.

I guess that wouldn't work for

EARNIE'S PARTS AND SERVICES

But if PARK and PARKK are the only conflicts, then it should work fine. I
find making such assumptions usually are not the case - thus my answer.

--
Regards,
Tom Ogilvy

"Bob C" wrote in message
...
Hi Bernie
So that's what happens after you read "can not find ISP"
I did post a thankyou and stated your code worked just
fine, but it got lost in transit.

Thankyou
Bob C

-----Original Message-----
Bob,

In case my post has been overlooked or lost:

Bob,

Replace PARK with a placeholder, do your replacement,
and restore the
PARKs. This will work as long as XXXX doesn't appear
anywhere in your
data set.

With Columns("B:B")
..Replace What:="PARK", Replacement:="XXXX",
LookAt:=xlPart
..Replace What:="PAR", Replacement:="PARK",
LookAt:=xlPart
..Replace What:="XXXX", Replacement:="PARK",
LookAt:=xlPart
End With

HTH,
Bernie
MS Excel MVP


"Bob C" wrote in
message
...
Hi Tom
I have just noticed in changing my question around, I
have deleted the part about doing this via macro.
Sorry and thankyou

Bob C

-----Original Message-----
I doubt that you can. Replace doesn't offer that
level
of sophistication to
the best of my knowledge.

You would have to write a macro with a specific
algorithm to identify this
situation and make the change.

--
Regards,
Tom Ogilvy

"Bob C" wrote in
message
...
Hi Everyone

I have a speadsheet with columns of credit card
transactions.
Column B contains the name & address of the
retailers
as
show below:

FOODLAND PARKLAND PAR
SAMS SEAFOOD RICHMOND PAR
SHELL SELF SERVE ELIZABETH VLE
DICK SMITH W8080 ELIZABETH CIT

How do I Find and Replace all instances of PAR on
the
right hand end of cell with PARK without changing
PARKLAND to PARKKLAND with two "K's" ?.

TIA

Bob C


.



.