CODEWITHSUNDEEP
excelvbaextract
user1660680
user1660680

Reputation: 97

VBA Excel - Extract each value surrounded by brackets

I want to extract the values surrounded by brackets.

text = "The man who {said} and also {var2} as well as {var3}

openingParen = InStr(text, "{")
closingParen = InStr(text, "}")
enclosedValue = Mid(text, openingParen + 1, closingParen - openingParen - 1)

phrasevariable1.Value = enclosedValue
phrasevariable2.Value = enclosedValue
phrasevariable3.Value = enclosedValue

This code only works for extracting the first value, is there a way to extract each of the variables, and put them into the textbox 1 -> n accordingly

Upvotes: 1

Views: 3714

Answers (3)

SWa
SWa

Reputation: 4363

Another way allowing for any number of matches:

Const stri As String = "The man who {said} and also {var2} as well as {var3}"
Dim sp

sp = Filter(Split(Replace(Chr(13) & stri, "}", "{" & Chr(13)), "{"), Chr(13), False)

Upvotes: 3

Alex K.
Alex K.

Reputation: 175826

You can use a regular expression for an unknown number of candidate tokens;

Dim matches As Object
Dim Re As Object: Set Re = CreateObject("vbscript.regexp")
Dim count As Long

Re.Pattern = "{(.*?)}"
Re.Global = True
Set matches = Re.Execute("The man who {said} and also {var2} as well as {var3}")

count = matches.count
For i = 0 To count - 1
    MsgBox matches(i).submatches(0)
Next

(Add a ref to Microsoft VBScript Regular Expressions for early binding)

Upvotes: 5

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

Assuming you always have three pairs of text in brackets, just create an array variable, and use split on your text string to get the values:

Dim myArray
myArray = Split(text, "{")

phasevariable1.Value  = Left(myArray(1), InStr(myArray(1), "}") - 1)
phasevariable2.Value  = Left(myArray(2), InStr(myArray(2), "}") - 1)
phasevariable3.Value  = Left(myArray(3), InStr(myArray(3), "}") - 1)

Upvotes: 1

Related Questions