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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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
580
Lee_ray
L
JenniferMurphy
  • Question Question
Comments on macro to help solve the problem of Excel changing column widths
2
Replies
11
Views
338
JenniferMurphy
JenniferMurphy
R
  • Question Question
Trying to find start and end times in a date/time column with a difficult twist.
Replies
8
Views
400
felixstraube
F
R
  • Question Question
Find and replace in word not working
Replies
1
Views
431
Rafaelete
R
O
  • Solved
VBA Code to Find Duplicate Values in a Range and Add a Number at the End
2
Replies
12
Views
664
OilEconomist
O

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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