string parsing in excel/vba separated by numbers and colons

Question

I've got a string set in VBA that I am pulling from another program. When I pull this data into Excel, it has the format:

EXAMPLE EXAMPLE EXAMPLE EXAMPLE 
EXAMPLE EXAMPLE EXAMPLE EXAMPLE 

001: EXAMPLE EXAMPLE EXAMPLE - EXAMPLE 

002: EXAMPLE EXAMPLE EXAMPLE - EXAMPLE

003: EXAMPLE EXAMPLE EXAMPLE - EXAMPLE 

With my current VBA code, you click a form control and it places that data into a cell just as I typed it. I want to separate it so when I click the control it places the data into separate cells separated by the numbers. That is,

EXAMPLE EXAMPLE EXAMPLE EXAMPLE 
EXAMPLE EXAMPLE EXAMPLE EXAMPLE 

001: EXAMPLE EXAMPLE EXAMPLE - EXAMPLE 

into the first cell,

002: EXAMPLE EXAMPLE EXAMPLE - EXAMPLE

into the adjacent cell, and

003: EXAMPLE EXAMPLE EXAMPLE - EXAMPLE

into the next adjacent cell, and so on for however many numbers I have. I hope I have explained my situating thoroughly enough for someone to help. Please excuse the fact that I am very new to VBA.

Answer 1

You could use Split and process the array into the cells. There's also a TextToColumns function on the Selection object.

Answer 2

Here is a post that we used multi delimiters split.

You may get an idea out of it.

• check beginning of the line starts with a number
• split by space, tab or any particular character than you have as the delimiter
• if you have multiple delimiters you may utilize the above mentioned method

Please comment with what you have tried out. Happy to help from there.

Answer 3

Use regular expressons. Add a reference to Microsoft VBScript Regular Expressions 5.5 from Tools -> References. Then you can write code like the following:

Public Function PasteValues()
Dim s As String, re As New RegExp
Dim matches As MatchCollection, m As Match

Dim rng As Range
'Destination workbook, worksheet within workbook, and starting cell
Set rng = ActiveWorkbook.Worksheets(1).Range("A1")

s = "EXAMPLE EXAMPLE EXAMPLE EXAMPLE " & Chr(13) & _
    "EXAMPLE EXAMPLE EXAMPLE EXAMPLE " & Chr(13) & _
    Chr(13) & _
    "001: EXAMPLE EXAMPLE EXAMPLE - EXAMPLE " & Chr(13) & _
    Chr(13) & _
    "002: EXAMPLE EXAMPLE EXAMPLE - EXAMPLE " & Chr(13) & _
    Chr(13) & _
    "003: EXAMPLE EXAMPLE EXAMPLE - EXAMPLE "

'Finds a sequence of non-digits (\D) followed by either 
    '1) a sequence of digits followed by a colon -- (\d*:)
    '2) the end of the string -- $
'The either/or is defined by the pipe -- |
re.Pattern = "(\D*)((\d*:)|$)"

'We want to match all instances, not just the first
re.Global = True

Set matches = re.Execute(s)
For Each m In matches
    'Each item in the SubMatches collection corresponds to a pair of parentheses.
    'e.g. m.SubMatches(0) returns the matched string corresponding to (\D*)
    'In this case, we aren't interested (I'm assuming) in the actual numbers, just that
    'they are there, but we could see them using SubMatches(1) or SubMatches(2)
    rng.Value = m.SubMatches(0)

    'Advance the range to the next column
    Set rng = rng.Offset(, 1)
Next
End Function