vba - Find / Replace Multiple Substrings from Table in Excel -
i'm copy/pasting text cell in excel has specific substrings need replacing, list of ~4,000 rows. have equal number of pieces of text/content number of rows, i'll need manually copy/paste in.
essentially, specific substrings need replaced same substring, wrapped html link, needs happen occurrences of substring list.
example:
i have text:
microsoft excel spreadsheet developed microsoft windows, mac os x, , ios.
in sheet #2, have following;
i need excel output text above, strings specified replaced links in following format:
<a href="http://www.apple.com/uk/mac/">mac</a>
in example, output following:
<a href="https://www.microsoft.com/">microsoft</a> excel spreadsheet developed <a href="https://www.microsoft.com/">microsoft</a> <a href="https://www.microsoft.com/en-gb/windows">windows </a>, <a href="http://www.apple.com/uk/mac/">mac</a> os x, , ios.
i'm no excel expert, i've not been able find solution. 1 answer on suggested following different question; =substitute(a2,"author","authoring") i'm not sure how edit include of strings without needing manually add every single 1 formula, replace each string 1 one.
has done similar before? if so, how did it?
the biggest problem face in situation false positives. when 1 word or phrase can found within word or phrase, may end 'double-processing' or wrongly processing search term. avoid this, process each term in descending order of length , in 2 steps. first, replace search term absolutely unique temporary string , once of terms have been reassigned, go , replace temporary strings actual html anchor elements.
module1 code sheet
option explicit public const csanchor string = "<a href=""×ll×"">×fn×</a>" sub processblurbs() dim m long, w long dim vwrds variant, vblrbs variant, vmsks variant dim rw long, r long, rndm long, str string 'apptggl btggl:=false 'uncomment after testing getreplacements vwrds 'debug.print lbound(vwrds, 1) & ":" & ubound(vwrds, 1) 'debug.print lbound(vwrds, 2) & ":" & ubound(vwrds, 2) worksheets("blurbs") if .autofiltermode .autofiltermode = false .cells(1, 1).currentregion 'replace associated guid unique-per-term .resize(.rows.count - 1, 1).offset(1, 1) 'reset column b column values .cells = .offset(0, -1).value2 w = lbound(vwrds, 1) ubound(vwrds, 1) debug.print vwrds(w, 1) & " - " & vwrds(w, 4) .replace what:=vwrds(w, 1), lookat:=xlpart, matchcase:=true, _ replacement:=vwrds(w, 4) next w end 'replace guids associated anchor elements .resize(.rows.count - 1, 1).offset(1, 1) w = lbound(vwrds, 1) ubound(vwrds, 1) .replace what:=vwrds(w, 4), lookat:=xlpart, matchcase:=true, _ replacement:=replace(replace(csanchor, "×ll×", vwrds(w, 2)), "×fn×", vwrds(w, 1)) next w end end end apptggl end sub sub getreplacements(byref wrds variant) worksheets("replacements") .cells(1, 1).currentregion .resize(.rows.count - 1, 1).offset(1, 2) .formular1c1 = "=len(rc1)" .value = .value2 end .resize(.rows.count - 1, 1).offset(1, 3) .formula = "=getguid()" .value = .value2 end .cells.sort key1:=.columns(3), order1:=xldescending, _ key2:=.columns(1), order2:=xlascending, _ orientation:=xltoptobottom, header:=xlyes .resize(.rows.count - 1, .columns.count).offset(1, 0) wrds = .cells.value2 end end end end sub function getguid() string dim tl object set tl = createobject("scriptlet.typelib") getguid = tl.guid set tl = nothing end function sub apptggl(optional btggl boolean = true) application .enableevents = btggl .screenupdating = btggl .displayalerts = btggl .calculation = iif(btggl, xlcalculationautomatic, xlcalculationmanual) end end sub step through repeated taps [f8] key idea of logic , process flow.
replacements worksheet
blurbs worksheet
results
lorem <a href="http://www.apple.com/uk/osx">mac os x</a> dolor sit amet, consectetur <a href="http://www.ibm.com/">ibm</a> elit. <br/> quisque <a href="http://www.ibm.com/">ibm</a> dolor <a href="http://www.apple.com/uk/mac">mac</a> ante vestibulum, eget <a href="https://www.microsoft.com/">microsoft</a> sapien tempus. <br/> duis tristique sapien non <a href="https://www.microsoft.com/">microsoft</a> <a href="https://www.microsoft.com/en-gb/windows">microsoft windows</a> porta. <br/> praesent <a href="http://www.apple.com/uk/ios">mac ios</a> urna id metus fringilla, non mattis sem porttitor. <br/> nunc bibendum <a href="http://www.ibm.com/">ibm</a> <a href="https://www.microsoft.com/en-gb/windows">microsoft windows</a> ligula varius vestibulum. <br/> nulla sollicitudin elit nec mauris <a href="http://www.ibm.com/">ibm</a> <a href="https://www.microsoft.com/en-gb/windows">microsoft windows</a> <br/> duis <a href="http://www.ibm.com/">ibm</a> <a href="http://www.apple.com/uk/osx">mac os x</a> id <a href="https://www.microsoft.com/en-gb/windows">microsoft windows</a> volutpat. <br/> nullam <a href="http://www.apple.com/uk/ios">mac ios</a> dolor sed <a href="https://www.microsoft.com/">microsoft</a> consequat <a href="https://www.microsoft.com/en-gb/windows">windows</a> quis eu purus. <br/> nullam <a href="https://www.microsoft.com/">microsoft</a> dolor eget <a href="https://www.microsoft.com/en-gb/windows">microsoft windows</a> <a href="http://www.apple.com/uk/osx">mac os x</a> <a href="http://www.apple.com/uk/ios">mac ios</a> <br/> vivamus <a href="http://www.apple.com/uk/mac">mac</a> leo non <a href="https://www.microsoft.com/en-gb/windows">windows</a> pharetra pretium malesuada dolor. <br/> donec condimentum leo <a href="https://www.microsoft.com/en-gb/windows">windows</a> dictum <a href="http://www.ibm.com/">ibm</a> <br/> nullam aliquam velit <a href="https://www.microsoft.com/en-gb/windows">windows</a> ullamcorper <a href="https://www.microsoft.com/">microsoft</a> <br/> curabitur <a href="https://www.microsoft.com/en-gb/windows">windows</a> leo eget magna eleifend, <a href="http://www.apple.com/uk/osx">mac os x</a> posuere velit tincidunt. <br/> aenean pulvinar quam <a href="http://www.apple.com/uk/mac">mac</a> <a href="http://www.apple.com/uk/ios">mac ios</a> <a href="https://www.microsoft.com/en-gb/windows">microsoft windows</a> <br/> aliquam <a href="https://www.microsoft.com/">microsoft</a> diam non ipsum egestas <a href="https://www.microsoft.com/en-gb/windows">microsoft windows</a> <br/> phasellus in lorem <a href="http://www.ibm.com/">ibm</a> <a href="http://www.apple.com/uk/ios">mac ios</a> mauris tempus, laoreet nunc. <br/> vestibulum accumsan justo eu <a href="http://www.ibm.com/">ibm</a> tristique, <a href="https://www.microsoft.com/en-gb/windows">microsoft windows</a> massa ornare. <br/> proin lobortis quam <a href="https://www.microsoft.com/en-gb/windows">windows</a> sem imperdiet, nec <a href="http://www.apple.com/uk/ios">mac ios</a> enim aliquet.
Comments
Post a Comment