0

I have a collection which has strings like:

130each 09/01/2017 09/01/2017
12each 09/01/2017 09/01/2017
1each 09/01/2017 09/01/2017

I get these values with the following code going through my excel sheet:

Dim col as New Collection
For i = 1 To lastRow
    If InStr(ws1.Cells(i, 1), "each")
        col.Add ws1.Cells(i, 1)
    End If
Next i

Now I want to separate the strings after the first space and store each part in two separate collections. Example second collection and third collection should contain the following:

130each [at index 0 of col2]
09/01/2017 09/01/2017 [at index 0 of col3]
12each [at index 1 of col2]
10/11/2017 10/11/2017 [at index 1 of col3]
...so on

Any idea on how to approach this I know I will be looping through the collection but how will i seperate into two seperate collections after the first space?

Jack
  • 275
  • 1
  • 6
  • 24
  • `Left(ws1.Cells(i, 1).Value, Instr(ws1.Cells(i, 1).Value, " ") - 1)` and `Mid(ws1.Cells(i, 1).Value, Instr(ws1.Cells(i, 1).Value, " ") + 1)` – YowE3K Aug 07 '17 at 05:29
  • 1
    @TimWilliams Passing `1` to `Split` returns an array with the entire string as the single element. Don't you mean `Split(stringHere, " " , 2)`? – Zev Spitz Aug 07 '17 at 05:42
  • @ZevSpitz - Yes, thanks. I confused "max # of elements" with "max # of splits" – Tim Williams Aug 07 '17 at 05:44
  • Look at this example for other ways to do this https://stackoverflow.com/questions/72210897/excel-how-to-split-cells-by-comma-delimiter-into-new-cells – Shane S Jan 06 '23 at 07:24

1 Answers1

1

Something like this:

Dim col2 As New Collection
Dim col3 As New Collection
Dim x As Variant

For Each x In col
    Dim parts As Variant
    parts = Split(x, " ", 2)
    col2.Add parts(0)
    col3.Add parts(1)
Next
Zev Spitz
  • 13,950
  • 6
  • 64
  • 136