regex - Excel VBA word boundaries (replacing prepositions) -

what need add plus + sign before every preposition in cell? reason, \b won't work me.

in javascript, should work fine:

(\bof\b|\bat\b) //change +$1 

but vba script in excel same regex won't match anything.

example:

И отдохнуть от них было для него спасением от мук. 

the result should be:

И отдохнуть +от них было +для него спасением +от мук. 

note "отдохнуть" word has "от" inside.

code (taken somewhere here):

sub replacewithre() dim re object 'regexp dim rng range, cl range dim sh worksheet dim wb workbook dim sreplace string dim areplace(0 1, 0 1) string dim long  set wb = activeworkbook set re = createobject("vbscript.regexp") ' new regexp re.global = true re.ignorecase = false re.multiline = true  ' load array of patterns , replacements areplace(0, 0) = "(\bот\b|\bдля\b)" areplace(0, 1) = "+$1"  each sh in wb.worksheets     on error resume next     set rng = sh.usedrange.specialcells(xlcelltypeconstants)     if err.number <> 0         err.clear     else         on error goto 0         each cl in rng             sreplace = cl.value             ' test each cell each pattern, replace when found             = 0 ubound(areplace, 1)                 re.pattern = areplace(i, 0)                 if re.test(sreplace)                     sreplace = re.replace(sreplace, areplace(i, 1))                 end if             next             cl.value = sreplace         next     end if next end sub 

i think got hint. looks regex works fine latin characters, cyrillic returns no matches. i've updated example.

as cyrillic characters, should aware per regular-expressions.info, vbscript regex has

no unicode support, except matching single characters with

so, \b matches @ non-word boundary , cyrillic letters non-words vbscript regex engine!

the way out expand \b this, e.g.:

strpattern = "(\s|^)(от|для)(\s|$)"  str = "Отдохнуть от них было для него спасением от мук и прекрасным отдыхом." strreplace = "$1+$2$3" 

i think can safely use spaces delimiters since prepositions not followed punctuation. can add punctuation first part though (just in case):

strpattern = "([\s,:;]|^)(от|для)(\s|$)"  

output:

Отдохнуть +от них было +для него спасением +от мук и прекрасным отдыхом.