How to separate substrings in a cell into array in vba

Question

I was wondering if anyone can give me some pointers. I have one column with data arrange like this in each cell:

Column E

E1-  name-age(nickname)   name-age(nickname)   name-age(nickname) ....
E2-  name-age(nickname)   name-age(nickname)   name-age(nickname) ....
etc...

the problem is its all in one column, and I know I can use text to column feature and delimit them by space. However, is there a more elegant way to do this? I'd like to do it over vba if possible.

My end goal is to capture each "name-age(nickname)" couplets into an array. Currently what I have started on vba -wise is

Sub SplitColumn()
    Details = Cells(i, 5).Value  // where the big column data is located
    tempString = Left(Details, InStr(Details, "  "))
End Sub

With this, I could only get the first name-age(nickname) couplet in that cell.. is there a better way to approch this?

Thanks in advance for help.

See this useful discussion of getting strings into arrays, especially with the Split function. — chuff
– chuff, Commented Feb 6, 2013 at 0:08

bonCodigo · Accepted Answer · 2013-02-06 01:16:48Z

1

You are naming your subroutine as Split columns but there's no split function used.

You have two options:

Split by space

E.g. cellvalue is just the variable name that refers to your cell. For multiple cells you may loop through.

  vArray As Variant
  vArray = Split(cellvalue, " ")

Make use of Text to Columns wizard in menu bar. Then transpose or dump the range into a variant array.

E.g.

vArray = Sheets(1).Range("A1:H10").value

edited Feb 6, 2013 at 1:16

answered Feb 6, 2013 at 1:11

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

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

Collectives™ on Stack Overflow

How to separate substrings in a cell into array in vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related