Insert character using Regex

R

Raj

Hi,

Please have a look at the three strings below which are in 3 cells in
a column. These are portions of longer strings.

KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384 SP-17700IOOI
KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938 RELIANCE FINANCIAL
KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401BHARTIA-3316776111

Using Regex and VBA, I want to add a "~" character after the "384" in
the first line, "938" in the second line and "401" in the third line
ie to get the following output. (Regex Buddy gave the following
regular expression "BBA.*?\d{11}" to match the pattern. Am not sure
that it would work in VBA.). I am new to regular expressions, but feel
that they would be the right approach to solving problems of this
type.

KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384~ SP-17700IOOI
KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938~ RELIANCE FINANCIAL
KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401~BHARTIA-3316776111

The pattern that is to be matched is "BBA followed by any characters,
followed by 11 numerical characters" or something other such pattern
that precisely determines the location of the "~" as above. I will
then use this knowledge to add more "~" characters in the string using
other Regex patterns.

Thanks in advance for the help.

Regards,
Raj
 
R

Ron Rosenfeld

Hi,

Please have a look at the three strings below which are in 3 cells in
a column. These are portions of longer strings.

KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384 SP-17700IOOI
KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938 RELIANCE FINANCIAL
KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401BHARTIA-3316776111

Using Regex and VBA, I want to add a "~" character after the "384" in
the first line, "938" in the second line and "401" in the third line
ie to get the following output. (Regex Buddy gave the following
regular expression "BBA.*?\d{11}" to match the pattern. Am not sure
that it would work in VBA.). I am new to regular expressions, but feel
that they would be the right approach to solving problems of this
type.

KR_Prod-Tlk-Np#-ll 1189S11 @as-BBAla#- 00000789384~ SP-17700IOOI
KR_Prod-Tlk-Np#-111189fflI@as-BBAla#- 00000788938~ RELIANCE FINANCIAL
KR_Prod-Tlk-Np#-l42000#lI @as-BBAla#- 00000789401~BHARTIA-3316776111

The pattern that is to be matched is "BBA followed by any characters,
followed by 11 numerical characters" or something other such pattern
that precisely determines the location of the "~" as above. I will
then use this knowledge to add more "~" characters in the string using
other Regex patterns.

Thanks in advance for the help.

Regards,
Raj
Click to expand...

========================================
Option Explicit
Sub InsertTilde()
Dim re As Object
Dim c As Range

Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(BBA.*?\d{11})"

For Each c In Selection
c.Offset(0, 1).Value = re.Replace(c.Value, "$1~")
Next c
End Sub
==========================================
--ron
 
R

Ron Rosenfeld

Thanks a ton, Ron. Exactly what I wanted.

Regards,
Raj
Click to expand...

Glad to help. Thanks for the feedback.

Looking back at an old thread you've previously posted, where the goal was to
split the string at that point, that could also be done with Regular
Expressions in VBA, with no need to insert a delimiter.

For example:

======================================
Option Explicit
Sub SplitSpecial()
Dim re As Object, mc As Object, m As Object
Const sPat As String = "^(.*?BBA.*?\d{11})\s*(.*)$"
'can omit \s* if do not want to trim leading spaces from
'second substring
Dim c As Range, rg As Range

Set re = CreateObject("vbscript.regexp")
With re
.MultiLine = True
.Pattern = sPat
.ignorecase = False
End With

Set rg = Range("A1:A3")
Range(rg.Offset(0, 1), rg.Offset(0, 2)).ClearContents
For Each c In rg
With c
If re.test(.Value) = True Then
Set mc = re.Execute(.Value)
.Offset(0, 1).Value = mc(0).submatches(0)
.Offset(0, 2).Value = mc(0).submatches(1)
End If
End With
Next c
End Sub
======================================
--ron
 
Top