Splitting varying length strings

[charville]

Hi,

I have a set of data that was extracted from an old 90's database into text which I have imported to Excel. My one issue is a single string of text containing 8 digit sequence numbers, but with no space or other delimiter. These cells (1000's) vary in length from 7 sequences on the smallest to 1027 in the largest. I need to keep the original and also convert it to individual cells on the same row containing each 8 digit number. Any help is appreciated, thanks!

 

[Rick Rothstein]

Can you show us a few representative (that is, different from each other) samples of single strings that you need to parse and show us what the parsed data looks like and tell us where it should be placed.

 

[charville]

Okay, the following is the string from a single cell, E2...
[TABLE="width: 568"]
<tbody>[TR]
[TD]00000608000006090000061000000611000006120000061300000614[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

I need to break it into individual cells containing these 8 digit sequences beginning at I2...[TABLE="width: 568"]
<tbody>[TR]
[TD]00000608 00000609 00000610 00000611 00000612 00000613 00000614
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
I could do some thing quick and not so elegant if they were all small like this, but these strings contain hundreds of 8 character groups in many cases. I can create a copy to upload without the proprietary info if necessary.

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub SequencesOfEightToACell()
  Dim R As Long, C As Long, LastRow As Long, MaxSeq As Long
  Dim Data As Variant, Result As Variant
  LastRow = Cells(Rows.Count, "E").End(xlUp).Row
  Data = Range("E2:E" & LastRow)
  MaxSeq = Evaluate("MAX(LEN(E2:E" & LastRow & "))")
  ReDim Result(1 To UBound(Data, 1), 1 To MaxSeq)
  For R = 1 To UBound(Data, 1)
    For C = 1 To MaxSeq Step 8
      If Len(Mid(Data(R, 1), C, 8)) Then
        Result(R, (C + 7) / 8) = Mid(Data(R, 1), C, 8)
      Else
        Exit For
      End If
    Next
  Next
  With Range("I2").Resize(UBound(Data), MaxSeq / 8)
    .NumberFormat = "@"
    .Value = Result
  End With
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (SequencesOfEightToACell) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peter_SSs]

.. these strings contain hundreds of 8 character groups in many cases.

This would allow for up to 1,000 8-digit numbers in a cell. You could adjust the 'Const' line down (or up if required), so long as you don't make it too small.

Test in a copy of your workbook.

Sub TTC()
  Dim ArrFieldInfo As Variant
  Dim i As Long
  
  Const max8digitnums As Long = 1000
  
  ReDim ArrFieldInfo(0 To max8digitnums - 1)
  For i = 0 To max8digitnums - 1
    ArrFieldInfo(i) = Array(i * 8, 2)
  Next i
  Range("E2", Range("E" & Rows.Count).End(xlUp)).TextToColumns Destination:=Range("I2"), DataType:=xlFixedWidth, FieldInfo:=ArrFieldInfo
End Sub

 

[FormR]

Here is a formula option you could also try in I2 copied down and across as required.

=MID($E2,1+((COLUMNS($I2:I2)-1)*8),8)

 

[charville]

That worked like a charm! Thanks a million!