1

I am familiar with the one cell into multiple columns feature when it comes to simple text. I have about 250 rows, where each row has a non-uniform number of list items in one cell. Some cells have as little as one list item, others have five. I need columns to be populated with each list item, separately. The columns are "First Item", "Second Item" etc. The items in the lists are non-uniform length strings.

Basically need a cell that says something like this "1. I have a dog 5. Cat 8. Today is Sunny", to have Column 1 say "1. I have a dog", Column 2 say "5. Cat", Column 3 say "8. Today is Sunny". Then process that for 250 rows.

This is some VBA I've tried, but it doesn't do anything. 

Sub test()
Dim text As String
Dim a As Integer
Dim name As Variant
text = ActiveCell.Value
name = Split(text, "##.")
End Sub
D. B.
  • 31
  • 2
  • So you split on the number? Will it always have a `.` after the number on which you want to split? Are those the only numbers in the strings? – Scott Craner Jul 21 '16 at 16:11
  • Yes, that is what I would like to do. But I need to preserve the number as part of the string. – D. B. Jul 21 '16 at 16:13
  • 1
    You may want to look into regex. See here: http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops – Scott Craner Jul 21 '16 at 16:16
  • Still not working when I replace the "##." with 2-25. – D. B. Jul 21 '16 at 17:46

0 Answers0