Dave Peterson offered one potential solution, but it wouldn't give you the 2
row solution you've asked for. The code below could be a solution to the
problem. It makes a couple of assumptions:
#1 that you put the 170 character code ribbon into cell A3 on a sheet, and
#2 that the results will be put into rows 1 and 2 of that same sheet, and
#3 that you have that sheet selected when you run this macro, AND
#4 BIG ONE - that the 170 characters start with an alpha group (abc), not a
group of digits as 123.
If you wish to retain leading zeros in numeric groups, you should format row
2 on the sheet as Text instead of zero, otherwise groups like "007" will
appear on the sheet in row two as 7.
To put the code into your workbook: Open the workbook, press [Alt]+[F11] to
open the VB Editor (VBE). In the VBE choose Insert | Module and then copy
the code below and paste it into the module presented to you. Close the VBE.
To use it: put your 170 character code ribbon into cell A3 of a sheet and
then choose Tools | Macro | Macros and select the SplitCodeRibbon macro and
click the [Run] button.
The code's probably not as tight/efficient as it could be, but it does seem
to meet one critical aspect of "good code": it produces reliable results when
used with data that meets the description you've provided for a 'code ribbon'.
Sub SplitCodeRibbon()
Const alphas = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Const digits = "0123456789"
Dim workingData As String
Dim alphaGroup As String
Dim digitsGroup As String
Dim positionPointer As Integer
'erase any previous entries in rows 1 & 2
ActiveSheet.Rows("1:2").ClearContents
'position for first entry
ActiveSheet.Range("A1").Activate
'get raw information to work with
workingData = ActiveSheet.Range("A3")
Application.ScreenUpdating = False ' for speed
Do While Len(workingData) > 0
'clear any previous results
alphaGroup = ""
digitsGroup = ""
positionPointer = 1
'assumes alpha group starts the whole thing
Do While InStr(alphas, UCase(Mid(workingData, _
positionPointer, 1)))
alphaGroup = alphaGroup & Mid(workingData, _
positionPointer, 1)
positionPointer = positionPointer + 1
Loop
workingData = Right(workingData, _
Len(workingData) - positionPointer + 1)
positionPointer = 1
Do While InStr(digits, UCase(Mid(workingData, _
positionPointer, 1)))
digitsGroup = digitsGroup & Mid(workingData, _
positionPointer, 1)
positionPointer = positionPointer + 1
'safety valve for last group
If positionPointer > Len(workingData) Then
digitsGroup = workingData
workingData = ""
positionPointer = 0
Exit Do '
End If
Loop
If positionPointer > 0 Then
workingData = Right(workingData, _
Len(workingData) - positionPointer + 1)
End If
ActiveCell = alphaGroup
ActiveCell.Offset(1, 0) = digitsGroup
ActiveCell.Offset(0, 1).Activate
Loop
End Sub
