|
Using Find & Replace in macro
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 |
Using Find & Replace in macro
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 |
Using Find & Replace in macro
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 |
Using Find & Replace in macro
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 . |
Using Find & Replace in macro
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 . |
Using Find & Replace in macro
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 . . |
Using Find & Replace in macro
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 . . |
Using Find & Replace in macro
To protect any PAR?, as long as ? is a capital letter, and XXX won't
appear in the database: Dim i As Integer With Columns("B:B") For i = 65 To 90 .Replace What:="PAR" & Chr(i), _ Replacement:="XXX" & Chr(i), _ LookAt:=xlPart Next i .Replace What:="PAR", Replacement:="PARK", LookAt:=xlPart For i = 65 To 90 .Replace What:="XXX" & Chr(i), _ Replacement:="PAR" & Chr(i), _ LookAt:=xlPart Next i End With HTH, Bernie MS Excel MVP "Tom Ogilvy" wrote in message ... 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 . . |
Using Find & Replace in macro
Hi Tom
Sorry about my reply post to you I thought you were referring to the Ctl H Find and Replace. The four examples are the only troublesome addresses. But I take onboard your concerns, it's one to be very very wary of using. Thankyou again Bob C -----Original Message----- 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 |
Using Find & Replace in macro
Hi again Bernie
Thankyou for the code. I'll try it out later on today after I get some sleep. It's three in the morning down under in Aussie Land. Thankyou for your replys and time. Bob C -----Original Message----- To protect any PAR?, as long as ? is a capital letter, and XXX won't appear in the database: Dim i As Integer With Columns("B:B") For i = 65 To 90 .Replace What:="PAR" & Chr(i), _ Replacement:="XXX" & Chr(i), _ LookAt:=xlPart Next i .Replace What:="PAR", Replacement:="PARK", LookAt:=xlPart For i = 65 To 90 .Replace What:="XXX" & Chr(i), _ Replacement:="PAR" & Chr(i), _ LookAt:=xlPart Next i End With HTH, Bernie MS Excel MVP "Tom Ogilvy" wrote in message ... 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 . . . |
Using Find & Replace in macro
They are one and the same. The Find used by Bernie is the VBA call to the
very same functionality called by the menu. -- Regards, Tom Ogilvy "Bob C" wrote in message ... Hi Tom Sorry about my reply post to you I thought you were referring to the Ctl H Find and Replace. The four examples are the only troublesome addresses. But I take onboard your concerns, it's one to be very very wary of using. Thankyou again Bob C -----Original Message----- 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 |
Using Find & Replace in macro
The four examples are the only troublesome addresses.
I think he is set for the 4 troublesome addresses. <g -- Regards, Tom Ogilvy "Bernie Deitrick" wrote in message ... To protect any PAR?, as long as ? is a capital letter, and XXX won't appear in the database: Dim i As Integer With Columns("B:B") For i = 65 To 90 .Replace What:="PAR" & Chr(i), _ Replacement:="XXX" & Chr(i), _ LookAt:=xlPart Next i .Replace What:="PAR", Replacement:="PARK", LookAt:=xlPart For i = 65 To 90 .Replace What:="XXX" & Chr(i), _ Replacement:="PAR" & Chr(i), _ LookAt:=xlPart Next i End With HTH, Bernie MS Excel MVP "Tom Ogilvy" wrote in message ... 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 . . |
| All times are GMT +1. The time now is 12:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com