Go back to your original thread... I posted a macro for you to try out.

--
Rick (MVP - Excel)



"Raj" wrote in message
...
Thanks a ton, Ron. Exactly what I wanted.

Regards,
Raj



On Apr 29, 7:00 am, Ron Rosenfeld wrote:
On Wed, 28 Apr 2010 18:28:35 -0700 (PDT), Raj wrote:
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

========================================
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