On Sun, 29 Jul 2012 18:48:00 +0000, dsimanton wrote:


I have several hundred rows of text which contains numbers. I need to
separate the segments from each other. For example: "1twinkle twinkle
little star 2how I wonder what you are 3up above the world 4so bright
5like a diamond 6in the sky 7shine so high 8twinkle twinkle 9little star
10how I wonder what you are"
The text between the numbers will vary in length and numbers may double
digit but will always be in numerical order but not necessarily starting
with 1.
I can do this with multiple steps: remove first number so as to be able
to find the second number and then trim the left portion up to the 2nd
number. Repeating this multiple times works. But there must be a simpler
way. I get bogged down though when the numbers become double digit. Help
please.

You can do this fairly easily with a VBA Macro, but your request does not indicate where these strings are located, and what you want to do with the result of splitting them. Also, it is not clear whether you want to retain the numbers of not.

The macro below will split the sentences at any whole number, and place it into a VBA array. For the sake of this exercise, I assumed your rows were in column A, and that you wanted the results displayed in the adjacent columns. I also assumed you wanted to retain any carriage returns in the original text.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

===================================
Option Explicit
Sub SplitOnNumber()
Dim re As Object, mc As Object, m As Object
Dim rSrc As Range, c As Range
Dim rDest As Range
Dim SplitArray()
Dim P As Long, I As Long
Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.Pattern = "\d+"
End With

For Each c In rSrc
Set mc = re.Execute(c.Text)
ReDim SplitArray(1 To mc.Count + 1)
P = 0
I = 1
For Each m In mc
SplitArray(I) = Mid(c.Text, P + 1, m.firstindex - P)
P = m.firstindex + m.Length
I = I + 1
Next m
SplitArray(I) = Mid(c.Text, P + 1)
Set rDest = c.Offset(columnoffset:=1)
Range(rDest, rDest(1, Columns.Count - 1)).ClearContents
Set rDest = rDest.Resize(columnsize:=I)
rDest = SplitArray
rDest.EntireColumn.AutoFit
Next c

End Sub
=======================================