Get a certain pattern for each cell of a column

Question

I have this excel file :

I would like to recover for the first column, a part of the content of each cell. However, a cell is in this form:

sentence [A/sentence] [B/sentence] [C/sentence] sentence

I would like to have only the patterns between [ ].

I have this code :

Sub groupByTypo2()
    Dim rng As Range, c As Range
    Dim dict As Object, dict2 As Object, dict3 As Object, col As Collection
    Dim dictFonctionnel As Object, dictTechnique As Object, dictSecurite As Object
    Dim v, w, k
    Dim sKey As String, S As String
    Dim I As Long

    'get the input range for the labels
    With ActiveSheet
        Set rng = .Range(.Range("C1"), .Cells(.Rows.Count, 3).End(xlUp))
    End With

    For Each c In rng.Cells
        v = Trim(c.Value)
        Debug.Print "valeur de v : " & v

         Z = Split(Trim(c.Offset(0, -2).Value))
            For Each w In Z
            Debug.Print "valeur de w : " & w

            Next w
    Next c

End Sub

And this result in output :

valeur de v : Fonctionnel
valeur de w : sentence
valeur de w : [A/sentence
valeur de w : 1]
valeur de w : [B/sentence
valeur de w : 2]
valeur de w : [C/sentence
valeur de w : 3]
valeur de w : sentence
valeur de v : Fonctionnel
valeur de w : sentence
valeur de w : [A/sentence
valeur de w : 4]
valeur de w : [B/sentence
valeur de w : 5]
valeur de w : [C/sentence
valeur de w : 6]
valeur de w : sentence
valeur de v : Securite
valeur de w : sentence
valeur de w : [A/sentence
valeur de w : 7]
valeur de w : [B/sentence
valeur de w : 8]
valeur de w : [C/sentence
valeur de w : 9]
valeur de w : sentence
valeur de v : Fonctionnel
valeur de w : sentence
valeur de w : [A/sentence
valeur de w : 10]
valeur de w : [B/sentence
valeur de w : 11]
valeur de w : [C/sentence
valeur de w : 12]
valeur de w : sentence
valeur de v : Technique
valeur de w : sentence
valeur de w : [A/sentence
valeur de w : 13]
valeur de w : [B/sentence
valeur de w : 14]
valeur de w : [C/sentence
valeur de w : 15]
valeur de w : sentence
valeur de v : Securite
valeur de w : sentence
valeur de w : [A/sentence
valeur de w : 16]
valeur de w : [B/sentence
valeur de w : 17]
valeur de w : [C/sentence
valeur de w : 18]
valeur de w : sentence

But I want to have :

valeur de v : Fonctionnel
valeur de w : [A/sentence 1]
valeur de w : [B/sentence 2]
valeur de w : [C/sentence 3]
valeur de v : Fonctionnel
valeur de w : [A/sentence 4]
valeur de w : [B/sentence 5]
valeur de w : [C/sentence 6]
valeur de v : Securite
valeur de w : [A/sentence 7]
valeur de w : [B/sentence 8]
valeur de w : [C/sentence 9]
valeur de v : Fonctionnel
valeur de w : [A/sentence 10]
valeur de w : [B/sentence 11]
valeur de w : [C/sentence 12]
valeur de v : Technique
valeur de w : [A/sentence 13]
valeur de w : [B/sentence 14]
valeur de w : [C/sentence 15]
valeur de v : Securite
valeur de w : [A/sentence 16]
valeur de w : [B/sentence 17]
valeur de w : [C/sentence 18]

I think the problem comes from this line :

Z = Split(Trim(c.Offset(0, -2).Value))

But I don't know how resolve it.

Thank you for your help !

Below answer may help you: [excel vba- extract text between 2 characters](https://stackoverflow.com/a/7293481/12906524) — Shivang Gupta, Mar 10 '20 at 10:08
`split` will not work in this case. instead use the regEx to get value between []. something like `Set regEx = CreateObject("VBScript.RegExp") regEx.Pattern = "\[(.*?)\]" Set matches = regEx.Execute(Trim(c.Offset(0, -2).Value))` — Maddy, Mar 10 '20 at 10:12
@Maddy Split can be used combined with a second spit, to target specific parts. You can *trap* specific text between 2 splits. — Foxfire And Burns And Burns, Mar 10 '20 at 14:55

Foxfire And Burns And Burns · Accepted Answer · 2020-03-10T14:53:41.927

Not sure if I replied your input properly, but this may work for you. The fake dataset I made is:

The code I've used:

Sub test()
Dim MiMatriz As Variant
Dim i As Long
Dim ZZ As Long
MiMatriz = Range("A3:A8").Value

For i = 1 To UBound(MiMatriz) Step 1
    For ZZ = 0 To 3 Step 1
        If ZZ > 0 Then
            Debug.Print "[" & Trim(Split(Split(MiMatriz(i, 1), "[")(ZZ), "]")(0)) & "]"
        Else
            Debug.Print Trim(Split(Split(MiMatriz(i, 1), "[")(ZZ), "]")(0))
        End If
    Next ZZ
Next i

Erase MiMatriz

End Sub

The output I get:

Fonctionnel
[A/sentence 1]
[B/sentence 2]
[C/sentence 3]
Fonctionnel
[A/sentence 4]
[B/sentence 5]
[C/sentence 6]
Securite
[A/sentence 7]
[B/sentence 8]
[C/sentence 9]
Fonctionnel
[A/sentence 10]
[B/sentence 11]
[C/sentence 12]
Technique
[A/sentence 13]
[B/sentence 14]
[C/sentence 15]
Securite
[A/sentence 16]
[B/sentence 17]
[C/sentence 18]

Anyways, I think it would be helpful if you could post a true data example (your input).

Hope this helps.

UPDATE: Looks like OP needs only the text inside brackets, so we need to adapt the ZZ Loop:

Sub test()
Dim MiMatriz As Variant
Dim i As Long
Dim ZZ As Long
MiMatriz = Range("A3:A8").Value

For i = 1 To UBound(MiMatriz) Step 1
    For ZZ = 1 To 3 Step 1
        Debug.Print "[" & Trim(Split(Split(MiMatriz(i, 1), "[")(ZZ), "]")(0)) & "]"
    Next ZZ
Next i

Erase MiMatriz

End Sub

The output of this updated code is:

[A/sentence 1]
[B/sentence 2]
[C/sentence 3]
[A/sentence 4]
[B/sentence 5]
[C/sentence 6]
[A/sentence 7]
[B/sentence 8]
[C/sentence 9]
[A/sentence 10]
[B/sentence 11]
[C/sentence 12]
[A/sentence 13]
[B/sentence 14]
[C/sentence 15]
[A/sentence 16]
[B/sentence 17]
[C/sentence 18]

Thank you for you answer ! In your exemple I need to delete all the pattern without [ ]. So i don't have to recover Fonctionnel, Securite, Technique... can you tell me what i need to change ? — Alain Al-Shikhley, Mar 10 '20 at 13:53

Get a certain pattern for each cell of a column

1 Answers1