| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . . |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find and replace macro | Excel Discussion (Misc queries) | |||
| Macro to Find and Replace | Excel Discussion (Misc queries) | |||
| Macro to Find & Replace | Excel Worksheet Functions | |||
| Macro: Find and replace | Excel Discussion (Misc queries) | |||
| Find & Replace in VB macro | Excel Discussion (Misc queries) | |||
![Bob C[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
