VBA RegEx extracting data from within a string -

i new regex , can't seem figure out why not working. had working lost data. have tried sorts of variations , can't remember did work before.

i have fields following variations

"sb123"
"some data sb 1234"
"some data sb 1234567 , more data"
"sb1234." etc...

and extract sb , number portion. can please help?

sub extractsbdata()  dim regex dim long dim pattern string set regex = createobject("vbscript.regexp") regex.ignorecase = true regex.global = true regex.pattern = ".*(sb\s*\d{3}\d+).*"      = 2 activesheet.usedrange.rows.count         if (regex.test(cells(i, 14).value))             cells(i, 16).value = regex.replace(cells(i, 14).value, "$1")         end if     next  end sub 

sorry, realized didn't explain it's doing. working if grouping surrounded data (ie "data here sb123 , here not "sb123 data here" or "data here sb123"). in addition, putting whole line in cells(i, 16) instead of sb number portion.

you may want try sb\s*\d{3,}.

if doesn't work (because of data preceding or following text you're looking for), remember . operator in regex matches every character except \n (newline). try [.\n]*sb\s*\d{3,}[.\n]*.

let me know if 1 of work you.