Find & Replace w/difficult edge cases

userxyz777

userxyz777

New Member
Joined
Apr 12, 2018
Messages
15
Hello everyone,

I have a find place problem I do not have the skills to solve

I want to replace all the commas + space ", " with a pipe |

I have some column data in a sheet called Have that looks like the following, where "one" is the column header

Code: 
[TABLE="width: 186"]
<tbody>[TR]
[TD]one[/TD]
[/TR]
[TR]
[TD]aa2[/TD]
[/TR]
[TR]
[TD]aa3, aa1, aa5[/TD]
[/TR]
[TR]
[TD]aa1, aa2[/TD]
[/TR]
[TR]
[TD]aa3, aa1, aa5[/TD]
[/TR]
[TR]
[TD]aa4[/TD]
[/TR]
[TR]
[TD]aa1, aa2[/TD]
[/TR]
[TR]
[TD]aa2, aa3, aa1[/TD]
[/TR]
[TR]
[TD]aa2, aa3, aa2[/TD]
[/TR]
[TR]
[TD]aa1[/TD]
[/TR]
</tbody>[/TABLE]

In a sheet called FindReplace, I have replacement values for column one in Have sheet

Code: 
[TABLE="width: 141"]
<tbody>[TR]
[TD]one[/TD]
[/TR]
[TR]
[TD]aa2, aa3[/TD]
[/TR]
[TR]
[TD]aa1[/TD]
[/TR]
[TR]
[TD]aa2[/TD]
[/TR]
[TR]
[TD]aa3[/TD]
[/TR]
[TR]
[TD]aa4[/TD]
[/TR]
[TR]
[TD]aa5[/TD]
[/TR]
</tbody>[/TABLE]

After find/Replace is ran I need column "one" to be

Code: 
[TABLE="width: 167"]
<tbody>[TR]
[TD="class: xl66, width: 167"]one[/TD]
[/TR]
[TR]
[TD="class: xl66"]aa2[/TD]
[/TR]
[TR]
[TD="class: xl66"]aa3|aa1|aa5[/TD]
[/TR]
[TR]
[TD="class: xl66"]aa1|aa2[/TD]
[/TR]
[TR]
[TD="class: xl66"]aa3|aa1|aa5[/TD]
[/TR]
[TR]
[TD="class: xl66"]aa4[/TD]
[/TR]
[TR]
[TD="class: xl66"]aa1|aa2[/TD]
[/TR]
[TR]
[TD="class: xl66"]aa2, aa3|aa1[/TD]
[/TR]
[TR]
[TD="class: xl66"]aa2, aa3|aa2[/TD]
[/TR]
[TR]
[TD="class: xl66"]aa1[/TD]
[/TR]
</tbody>[/TABLE]

But I can not figure out how to manage the edge cases where replace value can be a substring of a different replace value like "aa2, aa3|aa1"

Thanks for help on this
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
The edge cases must have a known unique pattern ID to identify them. One way to make it happen is splitting the column A using Text to Columns and then concatenate the columns again using something similar to =A2 & IF(ID = ?? ,",","|") & B2 & "|" & C2, but you need to have a way to define the ID = ?? to make it work for the string A. For example ID = ?? may be right(A2,1)=2, so if the first term is aa2, then the result will be a ',' separator. I hope it helps!
 
Upvote 0
Relating to your Basic Data, try this:-
The code should alter the data in sheet "Have"
Code: 
[COLOR="Navy"]Sub[/COLOR] MG27Oct39
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("FindReplace")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] InStr(Dn.Value, ", ") [COLOR="Navy"]Then[/COLOR] .Item(Dn.Value) = Empty
[COLOR="Navy"]Next[/COLOR]

[COLOR="Navy"]With[/COLOR] Sheets("Have")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
        [COLOR="Navy"]If[/COLOR] .exists(Left(Dn.Value, Len(K))) And K = Left(Dn.Value, Len(K)) [COLOR="Navy"]Then[/COLOR]
            Dn.Value = K & Replace(Right(Dn.Value, Len(Dn.Value) - Len(K)), ", ", "|")
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Thank you for the response,

When I run the code on the data provided, it works on the edge cases only. I get:

Code: 
[TABLE="width: 186"]
<colgroup><col></colgroup><tbody>[TR]
[TD]one[/TD]
[/TR]
[TR]
[TD]aa2[/TD]
[/TR]
[TR]
[TD]aa3, aa1, aa5[/TD]
[/TR]
[TR]
[TD]aa1, aa2[/TD]
[/TR]
[TR]
[TD]aa3, aa1, aa5[/TD]
[/TR]
[TR]
[TD]aa4[/TD]
[/TR]
[TR]
[TD]aa1, aa2[/TD]
[/TR]
[TR]
[TD]aa2, aa3|aa1[/TD]
[/TR]
[TR]
[TD]aa2, aa3|aa2[/TD]
[/TR]
[TR]
[TD]aa1[/TD]
[/TR]
</tbody>[/TABLE]

Need
Code: 
[TABLE="width: 167"]
 <colgroup><col width="167" style="width:125pt"> </colgroup><tbody>[TR]
  [TD="class: xl69, width: 167"]one[/TD]
 [/TR]
 [TR]
  [TD="class: xl69"]aa2[/TD]
 [/TR]
 [TR]
  [TD="class: xl69"]aa3|aa1|aa5[/TD]
 [/TR]
 [TR]
  [TD="class: xl69"]aa1|aa2[/TD]
 [/TR]
 [TR]
  [TD="class: xl69"]aa3|aa1|aa5[/TD]
 [/TR]
 [TR]
  [TD="class: xl69"]aa4[/TD]
 [/TR]
 [TR]
  [TD="class: xl69"]aa1|aa2[/TD]
 [/TR]
 [TR]
  [TD="class: xl69"]aa2, aa3|aa1[/TD]
 [/TR]
 [TR]
  [TD="class: xl69"]aa2, aa3|aa2[/TD]
 [/TR]
 [TR]
  [TD="class: xl69"]aa1[/TD]
[/TR]
</tbody>[/TABLE]

Thanks
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG27Oct58
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]With[/COLOR] Sheets("FindReplace")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] InStr(Dn.Value, ", ") [COLOR="Navy"]Then[/COLOR] .Item(Dn.Value) = Empty
[COLOR="Navy"]Next[/COLOR]


[COLOR="Navy"]With[/COLOR] Sheets("Have")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   fd = False
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
        [COLOR="Navy"]If[/COLOR] .Exists(Left(Dn.Value, Len(K))) And K = Left(Dn.Value, Len(K)) [COLOR="Navy"]Then[/COLOR]
            Dn.Value = K & Replace(Right(Dn.Value, Len(Dn.Value) - Len(K)), ", ", "|")
            fd = True
            [COLOR="Navy"]Exit[/COLOR] For
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] K

[COLOR="Navy"]If[/COLOR] Not fd [COLOR="Navy"]Then[/COLOR] Dn.Value = Replace(Dn.Value, ", ", "|")

[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Find and Replace VBA from a list in a 2nd sheet
Replies
4
Views
624
Lee_ray
L
I
  • Question Question
Advice to help find line of code please on my worksheet
Replies
4
Views
63
Alex Blakenburg
A
JenniferMurphy
  • Question Question
Comments on macro to help solve the problem of Excel changing column widths
2
Replies
11
Views
415
JenniferMurphy
JenniferMurphy
R
  • Question Question
Trying to find start and end times in a date/time column with a difficult twist.
Replies
8
Views
429
felixstraube
F
C
  • Solved
Calculating Totals between two column headers - Macro VBA
Replies
9
Views
123
Fuji
F

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top