Data Splitting

**daowen** · 02-22-2010, 02:37 PM

I am importing a text file into a sheet. The file imports with each line being one cell.

I need to break up the contents of the cell based on the character at the beginning of the line based on the first character of the line. I would like to have it put in the cells next to the line split up as follows

All lines beginning with "A"
1 9 10 4 6 5 1 6 11 52 <--Numbers are the positions in the field, EG A0000000019172400220514201005300220D000396
Becomes
A 000000001 9172400220 5142 010053 00220 D 000396

All lines beginning with a "Y:
1 15 30 3 5 12 39

All lines beginning with "C":
1 3 10 6 3 5 12 30 19 15 1

All lines beginning with "D":
1 3 10 6 1 3 5 12 30 19 15

All lines beginning with a "Z":
1 9 10 4 14 8 14 8 37

What do you think?

I just really need a hand with one of them and I can figure out the remainder from that.

Thanks,

D

**royUK** · 02-22-2010, 02:52 PM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**broro183** · 02-22-2010, 05:31 PM

hi Daowen,

A sample file will make it much easier for us to help, but I'll give it a go...

The below is incomplete & I'm sure it could be tidied up, but it may be enough to get you started.

option explicit
Sub VariableSplitting()
Dim OriArr As Variant    'Original array
Dim OriArrInd As Long    'Original array index
Dim OutputColBArr As Variant
Dim OutputColCArr As Variant
Dim OutputColDArr As Variant
Dim OutputColEArr As Variant
Dim OutputColFArr As Variant
Dim OutputColGArr As Variant
Dim OutputColHArr As Variant
Dim OutputColIArr As Variant
Dim OutputColJArr As Variant
Dim OutputColKArr As Variant
Dim OutputColLArr As Variant
Dim LastRw As Long
    Application.ScreenUpdating = False
    With ThisWorkbook.ActiveSheet
        LastRw = .Cells(.Rows.Count, "a").End(xlUp).Row
        OriArr = .Range("a2:a" & LastRw)
        OutputColBArr = .Range("b2:b" & LastRw)
        OutputColCArr = .Range("c2:c" & LastRw)
        OutputColDArr = .Range("d2:d" & LastRw)
    End With

    For OriArrInd = LBound(OriArr) To UBound(OriArr)
        Select Case UCase(Left(OriArr(OriArrInd, 1), 1))
            Case "A"
                'All lines beginning with "A"
                '1 9 10 4 6 5 1 6 11 52 <--Numbers are the positions in the field, EG A0000000019172400220514201005300220D000396
                'Becomes A 000000001 9172400220 5142 010053 00220 D 000396
                OutputColBArr(OriArrInd, 1) = TestAndAddAppostrophe(CStr(Left(OriArr(OriArrInd, 1), 1)))
                OutputColCArr(OriArrInd, 1) = TestAndAddAppostrophe(CStr(Mid(OriArr(OriArrInd, 1), 2, 9)))
                OutputColDArr(OriArrInd, 1) = TestAndAddAppostrophe(CStr(Mid(OriArr(OriArrInd, 1), 10, 10)))
                'OutputColEArr (OriArrInd)
                'OutputColFArr (OriArrInd)
                'OutputColGArr (OriArrInd)
                'OutputColHArr (OriArrInd)
                'OutputColIArr (OriArrInd)
                'OutputColJArr (OriArrInd)
                'OutputColKArr (OriArrInd)
            Case "Y"
                'All lines beginning with a "Y:
                '1 15 30 3 5 12 39
                OutputColBArr(OriArrInd, 1) = TestAndAddAppostrophe(CStr(Left(OriArr(OriArrInd, 1), 1)))
                OutputColCArr(OriArrInd, 1) = TestAndAddAppostrophe(CStr(Mid(OriArr(OriArrInd, 1), 2, 15)))
                OutputColDArr(OriArrInd, 1) = TestAndAddAppostrophe(CStr(Mid(OriArr(OriArrInd, 1), 16, 30)))
                'OutputColEArr (OriArrInd)
                'OutputColFArr (OriArrInd)
                'OutputColGArr (OriArrInd)
                'OutputColHArr (OriArrInd)
                'OutputColIArr (OriArrInd)
                'OutputColJArr (OriArrInd)
                'OutputColKArr (OriArrInd)
            Case "C"
                'All lines beginning with "C":
                '1 3 10 6 3 5 12 30 19 15 1
            Case "D"
                'All lines beginning with "D":
                '1 3 10 6 1 3 5 12 30 19 15
            Case "Z"
                'All lines beginning with a "Z":
                '1 9 10 4 14 8 14 8 37
        End Select
    Next OriArrInd
    With ThisWorkbook.ActiveSheet
        .Range("b2:b" & LastRw) = OutputColBArr
        .Range("c2:c" & LastRw) = OutputColCArr
        .Range("d2:d" & LastRw) = OutputColDArr
    End With
    Application.ScreenUpdating = True
End Sub

Function TestAndAddAppostrophe(IniStr As String) As String
    If Left(IniStr, 1) = 0 Then
        TestAndAddAppostrophe = "'" & IniStr
    Else
        TestAndAddAppostrophe = IniStr
    End If
End Function

hth
Rob