Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert character using Regex
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert character using Regex
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert character using Regex
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert character using Regex
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert character using Regex
On Wed, 28 Apr 2010 19:15:48 -0700 (PDT), Raj wrote:
Thanks a ton, Ron. Exactly what I wanted. Regards, Raj 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Tab Character | Excel Programming | |||
how to insert tab character into the cell? | New Users to Excel | |||
Insert Character into Editing-Cell | Excel Programming | |||
Insert Character in a String | Excel Programming | |||
Insert a special character before and after a word | Excel Discussion (Misc queries) |