CODEWITHSUNDEEP
excelvba
Alain Al-Shikhley
Alain Al-Shikhley

Reputation: 43

Split a string with Excel Vba and recover only an element between [ ]

I have a string of the form: 

"word 1, word 2, ..., word n, [Etiquette], many other words"

I would like to retrieve only "Etiquette" and put it in a variable.

For the moment I have for example v = "blabla, [Etiquette]"

I know I can use the function Split but when I just try to display Debug.Print Split(v) I get a type error.

Thank you for your help !

Upvotes: 0

Views: 855

Answers (3)

Dang D. Khanh
Dang D. Khanh

Reputation: 1471

This is an approach:

1, Filter(Split(x, ","), "[") get the strings contain " [...]"

2, Join these strings into one by join function (this avoids errors that occur when the filter return nothing)

3, remove characters "[" and "]" by the replace function

4, remove unnecessary spaces by trim function

. Here is the test code:

Sub Test()
x = "word 1, word 2, ..., word n, [Etiquette], many other words"
z = Trim(Replace(Replace(Join(Filter(Split(x, ","), "["), ","), "[", ""), "]", ""))
'z = Replace(Join(Filter(Split(Replace(x, "[", "]#"), "]"), "#"), ","), "#", "")'vs2
Debug.Print Z
End Sub

Upvotes: 7

T.M.
T.M.

Reputation: 9948

Mini-alternative

Just in addition to the above fine solution and in order to demonstrate that by adding the delimiter character to the string to be split you can avoid an error if Nothing is found.

Sub Example()
Const L = "[", R = "]", C = ","    ' used delimiters
Dim x As String, z As String
x = "word 1, word 2, ..., word n, Etiquette, many other words"

z = Split(Split(Split(x & L, L)(1) & R, R)(0) & C, C)(0)
Debug.Print "z = " & Chr(34) & z & Chr(34)
End Sub

Upvotes: 0

HTH
HTH

Reputation: 2031

you could simply use

Split(Split(v, "[")(1), "]")(0)

like for instance

Dim v As String, r As String
v = "word 1, word 2, ..., word n, [Etiquette], many other words"
r = Split(Split(v, "[")(1), "]")(0)

Upvotes: 2

Related Questions