VBA Returning Values from Split String

Question

I am attempting to return and define the split values of a defined string. (in vba) The delimiter of the cell is "@".

Here is an example: "Element1@Element2@Element3@Element4"

I have been successful in doing this when it is only "Element1@Element2" using the following code:

Sheet1.Range("B1").Value = Linerange.Value

Dim Element1 As String
Dim Element2 As String

Element1 = Left(Linerange.Value, InStr(1, Linerange.Value, "@") - 2)
Element2 = Right(Linerange.Value, Len(Linerange.Value) - InStr(1, Linerange.Value, "@") - 1)

Any idea how to approach this and define values for Element3 and Element4?

Thank you!

Have a look at the Split Function

tigeravatar
– tigeravatar

2017-07-26 19:16:00 +00:00
Commented Jul 26, 2017 at 19:16 — tigeravatar
– tigeravatar, Commented Jul 26, 2017 at 19:16

David Zemens · Accepted Answer · 2017-07-26 19:17:37Z

2

Try using the Split function on your value:

Dim elements, ele  '' Declared implicitly as Variant type

elements = Split(Linerange.Value, "@")
For Each ele in elements
    Debug.Print ele
Next

answered Jul 26, 2017 at 19:17

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

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

Comments

braX · Accepted Answer · 2017-07-26 19:18:05Z

0

Dim vSplit As Variant
vSplit=Split(Linerange.Value,"@")

You then have an array of vSplit()

answered Jul 26, 2017 at 19:18

braX

11.9k5 gold badges24 silver badges38 bronze badges

Collectives™ on Stack Overflow

VBA Returning Values from Split String

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related