regular expression in excel

[qiyusi]

Hello,

Anyone familiar with regex code in excel?

I try to learn regex code to pull year from cell, it work for the first occurrence, but i don't know how to pull 2nd or Nth occurence.

here's the example:

in A1 cells: 1998 1849 1958 2010

using the regex code: (\d{4}) it successfully pull data: 1998

i am try to pull second one which is : 1849 on example above

using the regex code: (?:\d{4}){1}(\d{4})
but not working, not sure if i am putting the right expression.

Hope anyone can give advise,

thanks,

[mancubus]

hi qiyusi,

you do not need to add [ask] before thread title. this is excel help forum and you are obviously asking for help. otherwise you would open your thread in non help forums of vbax.

you need to set the Global property to TRUE (the default is FALSE) in order to find all the matches.



Function RegExpNth(RegExpPattern As String, RegExpString As String, NthOcccurence As Long) As String

Dim RegExpMatches As Object

With CreateObject("VBScript.RegExp")
.Pattern = RegExpPattern
.Global = True
.MultiLine = False
.IgnoreCase = True
Set RegExpMatches = .Execute(RegExpString)
End With

RegExpNth = RegExpMatches(NthOcccurence)

End Function



how to use the UDF:


Sub TestUDF()
MsgBox RegExpNth("\d{4}", "1998 1849 1958 2010", 2)
End Sub

[qiyusi]

PERFECT!! Work Like a Charms!

Many thanks for this helpfull answer.

Note: apologies for the tag :) my behavior to use [ask] tag if i need to ask help from anyone in this forum.

[mancubus]

you are welcome.
no apology necessary. :)
thanks for marking the thread as solved.

[qiyusi]

:)Anyway, would be great if you can modify the function so it has ability to replace that nth occurence with other string/char. Same function with find and replace in built in excel.

[mancubus]

do you want to replace 2nd occurrence (1849) with, say, 9 and change "1998 1849 1958 2010" to "1998 9 1958 2010"


try this:


Sub TestUDF_withVariables()

Dim StrPattern As String, StrOriginal As String, StrNew As String, StrReplaceWith As String
Dim LngOccurrence As Long

StrPattern = "\d{4}"
StrOriginal = "1998 1849 1958 2010"
LngOccurrence = 2
StrReplaceWith = "9"

StrNew = Replace(StrOriginal, RegExpNth(StrPattern, StrOriginal, LngOccurrence), StrReplaceWith)

MsgBox StrNew

End Sub




or



Sub TestUDF_novariables()
MsgBox Replace("1998 1849 1958 2010", RegExpNth("\d{4}", "1998 1849 1958 2010", 2), "9")
End Sub

[mancubus]

this thread may help you as well. maybe you wil not need regex to achieve your requirement.

http://www.vbaexpress.com/forum/showthread.php?43429-Solved-VBA-Replace-using-regular-expressions

[qiyusi]

Thank Mancubus for your help and the link.
for the additional code above, i am not sure how to add it
here's the function i want:
13138

i want replace function as shown on the screenshot. how to add the code to the first code you post?

[mancubus]

Function ReplacePattern(InString As String, withPattern As String, OccNo As Long, ReplaceWith As String) As String
ReplacePattern = Replace(InString, RegExpFind(InString, withPattern, OccNo), ReplaceWith)
End Function



RegExpFind function can be found at:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=841

[qiyusi]

Nicely Done!

Thank you Master!

[mancubus]

you are welcome.

pls notice that if there is no match or occurence number is invalid (ie greater than total number of matches, if any) function returns the InString itself.