How to build regular expression to find n ocurrences of "[########]" in a cell

Question

I have a large table wich contains description + code of items in the following manner:

"description [#######]" 
(Code being enclosed in "[]" and composed of 8 numbers)

I need to separate code and description in different columns. The reason why regex are needed is that the above description is ussually found inside excel formulas like:=

=IF(xyz, "description1 [1######]", "description2 [2######]")

So that final result should be:

column 1: =IF(xyz, 1######, 2######)
column 2: =IF(xyz, "description1 ", "description2 ")

Has anyone done something similar? I found these answers somewhat related, but currently don't know enough to crack regex:

Returning a regex match in VBA (excel)

VBA Regular Expression to Match Date

So you're asking for the regex? If so, `\d{8}` will match exactly 8 numbers. — , Mar 26 '13 at 21:28
You don't say what version of Excel. Excel 2013's [FORMULATEXT](http://blog.contextures.com/archives/2013/01/31/show-formulas-with-formulatext-in-excel-2013/) function would be handy for this. — Doug Glancy, Mar 26 '13 at 22:09

Ross McConeghy · Answer 1 · 2013-03-26T22:33:56.557

This will iterate through the first 200 rows, this is very raw code with no error catching. It assumes there are always 8 numbers in the RE. If there is a syntax error in any of the formulas it will throw an error when trying to assign the errant formula to a cell.

Sub splitSpecial()
    Dim aParts As Variant
    Dim i As Long
    Dim RE As Object
    Dim ret As Object
    Dim sNewFormula As String

    Set RE = CreateObject("vbscript.regexp")

    For i = 1 To 200 'change 200 to be the last row
        aParts = Split(Range("A" & i).Formula, ",")
        RE.Pattern = "\[\d{8}\]"
        RE.Global = True
        Set ret = RE.Execute(Range("A" & i).Formula)
        If ret.Count <> 0 Then
            sNewFormula = aParts(0) & "," & Replace(Replace(ret.Item(0), "[", ""), "]", "") & _
                "," & Replace(Replace(ret.Item(0), "[", ""), "]", "") & ")"
            Range("B" & i).Formula = sNewFormula
            sNewFormula = aParts(0) & "," & Replace(aParts(1), ret.Item(0), "") & _
                "," & Replace(aParts(2), ret.Item(1), "")
            Range("C" & i).Formula = sNewFormula
        End If
    Next i

End Sub

Amazing!! I'll try it tonight and then i'll set the aswer acceptance accordingly. — A. Perez Cera, Mar 26 '13 at 22:55
+1 works, although you might want to remove the trailing space in ` "descriptionx "`. — brettdj, Mar 27 '13 at 05:26

score 1 · Accepted Answer · answered Mar 27 '13 at 05:25

1

Using a RegExp replace simplifies the replacement

This code uses a variant array on column A, results in column B to D

enter image description here

Sub Spliced()
Dim objRegex As Object
Dim objRegMC As Object
Dim X
Dim lngRow As Long
X = Range([a1], Cells(Rows.Count, "A").End(xlUp)).Formula
ReDim Preserve X(1 To UBound(X), 1 To 3)
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Global = True
    .Pattern = """(\w+) (\[)(\d{8})(\])"""

For lngRow = 1 To UBound(X)
If .test(X(lngRow, 1)) Then
X(lngRow, 2) = .Replace(X(lngRow, 1), "$3")
X(lngRow, 3) = .Replace(X(lngRow, 1), """$1""")
End If
Next
End With
[b1].Resize(UBound(X, 1), 3) = X
End Sub

answered Mar 27 '13 at 05:25

brettdj

54,857
16
114
177

Hey, I might be missing something, but when do you write on column D (4)? And i have never sin the syntax [a1] or [b1], what does it mean in code? Besides that +1 amazin job. – A. Perez Cera Mar 27 '13 at 08:35
Besides that, amazing job. I have the following regex it took me forever to build _"""[^,]+?(\[\d{8}\])"""_ using [RegExr](http://gskinner.com/RegExr/) which seems to work a little better. Please let your comments flow, i'll be eagerly waiting. – A. Perez Cera Mar 27 '13 at 08:42
@azperezC Thanks:) My regexp uses submatches (the `()` parts) with the `Replace` to avoid later further string manipulation with the `Execute` method. This is why my pattern is longer. I use an array which writes to columns B:D with this line `[b1].Resize(UBound(X, 1), 3) = X`. `[b1]` is the same as `Range("B1")` – brettdj Mar 27 '13 at 12:17
1

let me check if I understood this code: When you write '.Replace(X(lngRow, 1), "$3")' it means: replace the regex-found string with result from regex-section #3? – A. Perez Cera Mar 27 '13 at 13:51
@brettdj This is really excellent, I've learned several things from your code. I don't have any real experience with Regular Expressions I just know abstractly that they are very powerful. Thanks for the excellent example! Oh, and the `[a1]` notation! ... All in all, very meaty. Thanks again =) – Ross McConeghy Mar 28 '13 at 04:37
1

@RossMcConeghy Not so sure- you already did an excellent job! See [this article](http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html) by Patrick Matthews. It's a great resource. – brettdj Mar 28 '13 at 09:19

score 0 · Answer 3 · answered Mar 26 '13 at 21:33

0

If the code is ALWAYS enclosed in brackets and ALWAYS are 8 numbers... You could use regular text finding formulas.

    A1 = "DescriptionText1231 [CODE8DIG]"
    B1 = MID(B1,FIND("[",B1)+1,8)

Hope I understood your question.

answered Mar 26 '13 at 21:33

Mauricio

105
1
6

Yes you did, that answers part of it, since it won't work for the in-formula problem. – A. Perez Cera Mar 26 '13 at 22:02

How to build regular expression to find n ocurrences of "[########]" in a cell

3 Answers3