excel - Replace text marker with column text -

i having several columns text. furthermore, have column, called replacement text. columns contains strings have markers in it, [1], [2], etc.

i replace markers text in marker`s row.

for example, here can find [5] becomes here can find b, because [5] markers column , in row of string b value marker.

i thinking of creating large if-else construct , substitute text, extremely error-prone.

however, kindly ask if there easier solution?

i appreciate input!

replacebrackets1: uses regex extract column number. takes 15.03 seconds processed 100k records.

---

sub replacebrackets1()     'http://analystcave.com/excel-regex-tutorial/     dim c range     dim match object, matches object, regex object     set regex = createobject("vbscript.regexp")      regex         .global = true         .pattern = "\[(.*?)\]"     end      each c in range("k3", range("k" & rows.count).end(xlup))         if regex.test(c.text)             set matches = regex.execute(c.text)             each match in matches                 c.replace match, c.entirerow.columns(cint(match.submatches(0)))             next match          end if     next end sub 

replacebrackets2: loades data arrays, uses regex extract column number , writes worksheet 1 time. takes 1.27 seconds process 100k records.

---

sub replacebrackets2() 'http://analystcave.com/excel-regex-tutorial/     dim x long, column long     dim ardata, values     dim match object, matches object, regex object     set regex = createobject("vbscript.regexp")      regex         .global = true         .pattern = "\[(.*?)\]"     end      values = range("k3", range("k" & rows.count).end(xlup))     ardata = range("a3", "l" & ubound(values, 1) + 2)      x = 1 ubound(values, 1)         if regex.test(values(x, 1))             set matches = regex.execute(values(x, 1))             each match in matches                 column = match.submatches(0)                 values(x, 1) = ardata(x, column)             next match         end if     next      range("k3", range("k" & rows.count).end(xlup)) = values  end sub 

after converting replacebrackets1 udf (getreplacedtext) amazed find took 2.53 seconds fill formula in 100k records. i'm not sure way faster original. having many formulas slows down spreadsheet.

getreplacedtext: uses static regex parse data.

function getreplacedtext(replacementtext string, source range) 'http://analystcave.com/excel-regex-tutorial/     dim match object, matches object     static regex object     if regex nothing         set regex = createobject("vbscript.regexp")          regex             .global = true             .pattern = "\[(.*?)\]"         end     end if      if regex.test(replacementtext)         set matches = regex.execute(replacementtext)         each match in matches             replacementtext = replace(replacementtext, match, source.columns(cint(match.submatches(0))))         next match      end if      getreplacedtext = replacementtext end function