how to split string using multichar string as delimiter in vba (excel)

Question

Please tell me how to split a string into parts, using as a separator a multichar string, not a single symbol

for example

array = MySplit("xdatayydatazzz", "data");

and result

array(0) = "x"
array(1) = "yy"
array(2) = "zzz"

E. Villiger · Accepted Answer · 2017-11-19 10:49:11Z

4

Um, why not just use:

arr = Split("xdatayydatazzz", "data")

Just to be sure, I tested this, and it works as expected. Docs on Split here.

answered Nov 18, 2017 at 18:25

E. Villiger

92612 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

you could not have tested it in VBA. the variable name array is illegal. the semicolon at the end of line is also illegal

It does work though. I wondered if it did, so thanks for checking!

Haha, true, I just tested the concept, not the exact spelling, thanks for the tip.

QHarr · Accepted Answer · 2017-11-18 18:01:37Z

3

First do a replace on the string e.g. data with a chosen character e.g. @ and then split on that:

secondstring = Replace("xdatayydatazzz", "data", "@")

array = Split(secondstring, "@")

Could nest the Replace inside the Split to keep to one step.

answered Nov 18, 2017 at 18:01

QHarr

84.5k14 gold badges58 silver badges105 bronze badges