Need Help Parsing a Record Layout

[perryjasonc]

I receive a record every day that has several sequences. Each sequence is a row followed by a string that is all in the same column. I need Excel to break apart these sequences into certain lengths that represent data such as names and account numbers. Below is an example of what the raw data looks like.

[TABLE="width: 64"]
<tbody>[TR]
[TD]F51010058 080705P2031234567890 12345678901123456789012345 123456789012345
[TABLE="width: 64"]
<tbody>[TR]
[TD]F51020058SCHOLARSCHOICECOLLEGESVGSPRGM JANEDOE
[TABLE="width: 64"]
<tbody>[TR]
[TD]F51030058FBOJOHNDOE 12345CHRISTMASDR
[TABLE="width: 64"]
<tbody>[TR]
[TD]F51040058 NORTHPOLE CO021

But I can't just simply to text to columns, because the data in each row I need to parse isn't the same length.

Does anyone know how to make a macro that can identify the first 5 characters in the string, then use a MID formula or something like that to cut up the rest of the string and move certain data elements to a new sheet in a specific column?

Sorry, I know that is vague but I am in a rush. Will clarify more if needed.[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Joe4]

Welcome to the Board!

Note that "Text to Columns" can delimit data in multiple ways:
- by a defined delimiter (like comma, space, tab, etc)
- by Fixed Width

Based on your data, I am not quite clear on how it should determine where/how things should be split up.
Could you post a few examples, along with the expected results (and explain exactly how you got to those expected results in your examples)?
I think we need to see/understand the exact logic you are using here.

 

[perryjasonc]

Thank you for taking the time to reply.

Now that I am not rushing around, I've thought about my problem in a different way. What would be more helpful to me is way to move a row to the one above it if it starts with a certain value. Then do that over and over again. My data is several hundred thousand rows.

F51010058 080705P2031234567890
F51020058SCHOLARSCHOICECOLLEGESVGSPRGM
F51030058FBOJOHNDOE
F51010058 080705P2031234567890
F51020058SCHOLARSCHOICECOLLEGESVGSPRGM
F51030058FBOJOHNDOE

If my data looks like what I have above, I would like for excel to be able to identify that the second row starts with "F5102" then add that to the end of the row above it that starts with "F5101". Then do that for everything that starts with "F51##" until it finds an "F5101" again. Then I can use Text to Columns after that the break apart the string as needed.

 

[Fluff]

Re: Code to Cut and Paste Data to the Row Above

As you haven't said where your data is located I've used col A starting in A1.
I've also assumed that the first value (A1 in this case) will start with F5101

Sub ConcatRws()

    Dim Cl As Range
    Dim Rng As Range
    
    For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
        If Left(Cl.Value, 5) = "F5101" Then
            Set Rng = Cl
        Else
            Rng.Value = Rng.Value & [COLOR=#ff0000]""[/COLOR] & Cl.Value
            Cl.ClearContents
        End If
    Next Cl
    [COLOR=#0000ff]Columns(1).SpecialCells(xlBlanks).EntireRow.Delete[/COLOR]
    
End Sub

If you need a delimiter added, then added it between the quotes in red.
At the moment this will delete the entire row where data has been removed, if you don't want that to happen, then remove the line in blue.

 

[Joe4]

Sorry, I have been away for a few days.

Try this:

Sub MyCombineMacro()

    Dim lastRow As Long
    Dim r As Long
    Dim cell As Range

    Application.ScreenUpdating = False

'   Find last row with data in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows, starting at the bottom
    For r = lastRow To 2 Step -1
        Set cell = Cells(r, "A")
        If Left(cell, 5) <> "F5101" Then
            cell.Offset(-1, 0) = cell.Offset(-1, 0) & Mid(cell, 10)
            Rows(r).Delete
        End If
    Next r
    
    Application.ScreenUpdating = True

End Sub

 

[Rick Rothstein]

If you have less than 65,000 rows of data, then here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub MyCombineMacro()
  Dim Grouped As Variant
  Grouped = Application.Transpose(Split(Mid(Join(Application.Transpose(Evaluate(Replace("(IF(LEFT(@,5)=""F5101"",""|""&@,MID(@,10,LEN(@))))", "@", "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row))), ""), 2), "|"))
  Range("B1").Resize(UBound(Grouped)) = Grouped
End Sub[/td]
[/tr]
[/table]