I receive code ribbons of 170 characters long.
eg:aar34cvf5hyj8dfg23bhy8.............
how can I split these automatically into
a1: aar a2:34
b1:cvf b2:5
these ribbons very in lead dut 2 to the different figures. they are allso no
spaces in between

Maybe...

Select the range and do data|text to columns (in xl2003 menus).

You'll be shown a wizard. You can specify that your text is made up of fixed
width fields. You can draw lines at each each field and excel will follow your
rules to parse the data into different columns.

3e wrote:

I receive code ribbons of 170 characters long.
eg:aar34cvf5hyj8dfg23bhy8.............
how can I split these automatically into
a1: aar a2:34
b1:cvf b2:5
these ribbons very in lead dut 2 to the different figures. they are allso no
spaces in between

--

Dave Peterson

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


"3e" wrote:

I receive code ribbons of 170 characters long.
eg:aar34cvf5hyj8dfg23bhy8.............
how can I split these automatically into
a1: aar a2:34
b1:cvf b2:5
these ribbons very in lead dut 2 to the different figures. they are allso no
spaces in between