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

Question

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 !

Split return a array of string you can loop through split or just display it like `Split(v)[1]`. — Deepak, Mar 29 '20 at 14:33

Dang D. Khanh · Accepted Answer · 2020-03-29T18:35:26.287

7

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

edited Mar 29 '20 at 18:35

answered Mar 29 '20 at 14:44

Dang D. Khanh

1,440
6
13

3

clever use of `Filter()` to locate the correct array member. – Gary's Student Mar 29 '20 at 14:50
thanks @Gary'sStudent , I think depending on the input string too, because `mid, instr` is also very effective – Dang D. Khanh Mar 29 '20 at 15:02
My variable x is x = Trim(c.Value). And with your example I have an error "the index does not belong to the selection". But when I tried with x = "blabla, [Etiquette]", it works. Can you explain me why please ? – Alain Al-Shikhley Mar 29 '20 at 15:50
@alain: because `Filter(Split(x, ","), "[")` return nothing if not exist string, so ``Filter(Split(x, ","), "[")(0)` become nothing(0) -> an error occur. I have updated the code so that the code does not occur. – Dang D. Khanh Mar 29 '20 at 16:17
FYI added an alternative to demonstrate how to avoid an error raised if nothing is found :) – T.M. Mar 29 '20 at 17:57
Thank you very much for your explication ! – Alain Al-Shikhley Mar 29 '20 at 18:28

score 2 · Answer 2 · answered Mar 29 '20 at 17:45

2

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)

answered Mar 29 '20 at 17:45

HTH

2,031
1
4
10

Friendly hint: correct approach; would raise an error, however if no finding in brackets - see OP and my answer :) – T.M. Mar 29 '20 at 17:50
1

I'm just answering the question, where the string example do have a word in brackets and the request is _"I would like to retrieve only "Etiquette" and put it in a variable."_ – HTH Mar 29 '20 at 18:00
Thank you I will try your example too ! – Alain Al-Shikhley Mar 29 '20 at 18:28
@alain, did you try my example? – HTH Mar 30 '20 at 11:43
Fine. And what solution are you going to use? – HTH Apr 02 '20 at 14:40

score 0 · Answer 3 · answered Mar 29 '20 at 17:48

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

Thank you I will try your example too ! – Alain Al-Shikhley Apr 02 '20 at 14:41 — Alain Al-Shikhley, Apr 02 '20 at 14:41

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

3 Answers3

Linked