Excel VBA字边界(替换介词) [英] Excel VBA word boundaries (replacing prepositions)
问题描述
我需要做的是在单元格中的每个介词之前添加一个加号 +
由于某些原因, \b
将不适用于我。
在JavaScript中,它应该工作正常:
(\bof\b | \bat\b)
// $ with
+ $ 1
但是,具有相同正则表达式的Excel中的VBA脚本将不匹配任何内容。 p>
示例:
Иотдохнутьотнихбылодлянегоспасениемотмук。
结果应该是:
Иотдохнуть+отнихбыло+длянегоспасением+отмук。
请注意,отдохнуть字里面有от。
代码本身(在某处某处):
Sub ReplaceWithRe()
Dim re As Object 'RegExp
Dim rng As Range,cl As Range
Dim sh As Worksheet
Dim wb As Workbook
Dim sReplace As String
Dim aReplace(0 to 1,0为1)As String
Dim i As Long
设置wb = ActiveWorkbook
设置re = CreateObject(vbscript.regexp)'新的RegExp
re.Global = True
re.IgnoreCase = False
re.MultiLine = True
'加载模式和替换数组
aReplace(0,0)=(\
aReplace(0,1)=+ $ 1
对于每个sh在wb.Worksheets
错误恢复Next
设置rng = sh.UsedRange.SpecialCells(xlCellTypeConstants)
如果Err.Number<> 0然后
Err.Clear
Else
对错误GoTo 0
对于每个cl在rng
sReplace = cl.Value
'每个测试每个单元格模式,替换发现
对于i = 0到UBound(aReplace,1)
re.Pattern = aReplace(i,0)
如果re.Test(sReplace)然后
sReplace = re.Replace(sReplace,aReplace(i,1))
结束如果
下一个
cl.Value = sReplace
下一个
如果
Next
End Sub
我想我有一个提示。 200新新新新旗新新新旗新新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新新旗新旗新我已经更新了例子。
至于西里尔字符,你应该注意,根据 Regular-expressions.info ,VBScript正则表达式具有
没有Unicode支持,除了匹配单个字符与
所以, \b
在非字边界匹配,西里尔字母是VBScript正则表达式引擎的非单词!
唯一的出路是扩展 \b
这样,例如:
strPattern =(\ s | ^)(от|для)(\s | $)
str =Отдохнутьотнихблддннмм
strReplace =$ 1 + $ 2 $ 3
我想我们可以安全地使用空格作为分隔符因为介词通常不是标点符号。您可以添加第一部分的标点符号(以防万一):
strPattern =([\s, ] | ^)(от|для)(\s | $)
输出: p>
Отдохнуть+отнихбыло+длянегоспасением+отмукипрекраснымотдыхом。
What I need to do is to add a plus +
sign before every preposition in the cell? For some reason, \b
won't work for me.
In JavaScript, it should work just fine:
(\bof\b|\bat\b)
//change with
+$1
But VBA script in Excel with the same regex won't match anything.
Example:
И отдохнуть от них было для него спасением от мук.
The result should be:
И отдохнуть +от них было +для него спасением +от мук.
Note that "отдохнуть" word has "от" inside.
Code itself (taken somewhere here):
Sub ReplaceWithRe()
Dim re As Object 'RegExp
Dim rng As Range, cl As Range
Dim sh As Worksheet
Dim wb As Workbook
Dim sReplace As String
Dim aReplace(0 To 1, 0 To 1) As String
Dim i As Long
Set wb = ActiveWorkbook
Set re = CreateObject("vbscript.regexp") ' New RegExp
re.Global = True
re.IgnoreCase = False
re.MultiLine = True
' Load array of patterns and replacements
aReplace(0, 0) = "(\bот\b|\bдля\b)"
aReplace(0, 1) = "+$1"
For Each sh In wb.Worksheets
On Error Resume Next
Set rng = sh.UsedRange.SpecialCells(xlCellTypeConstants)
If Err.Number <> 0 Then
Err.Clear
Else
On Error GoTo 0
For Each cl In rng
sReplace = cl.Value
' Test each cell for each pattern, replace when found
For i = 0 To UBound(aReplace, 1)
re.Pattern = aReplace(i, 0)
If re.Test(sReplace) Then
sReplace = re.Replace(sReplace, aReplace(i, 1))
End If
Next
cl.Value = sReplace
Next
End If
Next
End Sub
I think I got a hint. It looks like this regex works fine with Latin characters, but with Cyrillic it returns no matches. I've updated example.
As for Cyrillic characters, you should be aware that as per Regular-expressions.info, VBScript regex has
No Unicode support, except for matching single characters with
So, \b
matches at a non-word boundary and the Cyrillic letters are non-words for VBScript regex engine!
The only way out is to expand the \b
like this, e.g.:
strPattern = "(\s|^)(от|для)(\s|$)"
str = "Отдохнуть от них было для него спасением от мук и прекрасным отдыхом."
strReplace = "$1+$2$3"
I think we can safely use spaces as delimiters since prepositions are not usually followed by punctuation. You can add punctuation to the first part though (just in case):
strPattern = "([\s,:;]|^)(от|для)(\s|$)"
Output:
Отдохнуть +от них было +для него спасением +от мук и прекрасным отдыхом.
这篇关于Excel VBA字边界(替换介词)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!