Excel - Text To Columns but skip some rows

[hooverJones]

I am wondering if it is possible to perform a text-to-columns operation and only split some rows into columns? For instance, if a row starts with a specific phrase, keep that row all in one column, but split all the other rows delimited by size.

Thanks!
HooverJones

[Rudi]

Hi HooverJones,

Text to Columns can split at delimiters, so it is possible to split only certain phrases if you set up the phrases to contain the delimiter at specific points. Any phrase without the delimiter will be ignored.
I am not sure if what you mean by, "split all the other rows delimited by size", you imply to split by length of text?

Can you provide a sample of what you intend to do.

If Text to Columns cannot meet your need, I'm sure one of us can assist with a macro to process the phrases to your specific conditions.

[hooverJones]

Hi Rudi, Thanks for your offer of assistance. I have a text file to import into Excel and it contains two types of rows; if the the row starts with Subtotal the first part of it needs to be kept together in one cell for legibility and the last part of the row line up in the amount columns. The first part of the Subtotals line isn't always the same length. I have attached an example Excel file to show how the final product should look and also attached an example input file.

[Rudi]

Hi,

I have a process to you can follow, but automating it is going to be a challenge!

1. One would import the file as fixed width, only splitting the value columns (see import image below)
2. Once the file is imported, insert column A and number all the rows 1..n
3. Filter out all the subtotal entries and copy the entire row to a new temporary sheet
4. Delete the rows containing the subtotal entries in the source
5. Now split the description column by delimiter (with space delimiter and treat consecutive delimiters as one)
6. Once this sheet is split correctly, copy the subtotal rows (from the temporary sheet) back into the source sheet appending them below the last row
7. Move the values for these subtotal rows to line up with the values from the split columns above
8. Sort the 1..n numbers back into ascending order to move the records back into its original order. (see final image below)

Import.png

This will produce the version as seen in the image below

Final.png

As I mentioned, creating a macro to automate this is going to be a challenge due to the fact that the source content is not properly delimited.

[hooverJones]

Thanks for all the work you did! I had the same thought of numbering the lines and moving the Subtotals lines out of the way and then putting them back in. However, I do have some control over how the text file is created - it actually starts out as a formatted report, with headings on each page and page numbers and spacing lines. I take it through a process to "flatten" it out, removing blank lines, and only keeping the first page column headers. I could work on making the input file contents delimited to make it easier to use the Excel text-to-columns!

It is easier for me to write the code and logic for the "flattening" process than to write macros, but I am getting a little better with practice.

I had two young Steller's Jays making a big racket up on my roof this morning.

Thanks again!
Kristi

[Rudi]

First time I see Stellars
My avatar is a Blue Jay, but I quite fancy the ones you pointed to.

If you want to pursue some automation with macros on a flat file that lends itself easier to the layout you need, the Excel enthusiasts here will be happy to assist...
Up to you.

[hooverJones]

The Stellars Jays have such a distinctive look, mohawk hair cut and very bright blue. They love the birdbath and make a production out of it, splashing all the water out in the process.

I would be very happy to have you Excel enthusiasts lend your assistance! Thanks very much!

[Rudi]

I've had a look at images on Google and the Stellars crown (or mohawk) is twice the size of the Blue Jay. It does make it very distinctive!

If you could post a sample based on the "controlled" format you referred to above, we can evaluate if it's structure could work better for automation.

[hooverJones]

Ok, I have attached the file in it's current condition. I could place characters to use as delimiters before and after the portion of the Subtotals line that I need kept together.

Do let me know what other suggestions you have for massaging this input file.
sustenance for all your hard work!

[Rudi]

TX for the choccies...

This code works on the current structure of the data you supplied. There is no guarantee it working on the file if the structure is different.
Please note that there is no checks or conditioning built in to validate the structure.

You can copy the code into a standard VBA module (press ALT+F11 to access the VBA Editor and select Insert > Module from the menus)
Then run the macro from Excel (press ALT+F8, then select ProcessText)
The macro will prompt you to browse for the text file which will import onto the current sheet and get processed.

Code: Select all

Sub ProcessText()
Dim sFile As String
    sFile = Application.GetOpenFilename("Text Files (*.txt),txt", , "Browse for the source text file...")
    Application.ScreenUpdating = False
    If sFile = "False" Then Exit Sub
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & sFile, Destination:=Range("$A$1"))
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(88, 8, 15, 15)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Columns("A:A").Copy
    Columns("F:F").Insert Shift:=xlToRight
    Columns("F:F").AutoFilter Field:=1, Criteria1:="=*Subtotals*", Operator:=xlAnd
    Columns("F:F").ClearContents
    Columns("F:F").TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(17, 1), Array(34, 1), Array(48, 1), _
        Array(66, 1)), TrailingMinusNumbers:=True
    Columns("F:K").Cut
    Columns("B:G").Insert Shift:=xlToRight
    Columns("A:A").AutoFilter Field:=1, Criteria1:="<>*Subtotals*", Operator:=xlAnd
    Range(Range("A4"), Range("A4").End(xlDown)).ClearContents
    Columns("A:A").AutoFilter
    Range("B2", Cells(4, Columns.Count).End(xlToLeft).Offset(-1)).ClearContents
    With Range("A4", Cells(4, Columns.Count).End(xlToLeft))
        .Font.Bold = True
        .Interior.Color = RGB(189, 215, 238)
        .Borders(xlEdgeBottom).Weight = xlThin
    End With
    Cells.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

[Rudi]

Hmmm... I notice there is a discrepancy with the column headers;
Cov, Month and Carrier and the column content do not match. (I overlooked that).

I can try fix, but its late here and I have to go to bed. If nobody else attempts a fix, I'll see if I can tweak the code tomorrow!

[hooverJones]

NO worries, I can handle that sort of thing! Thanks again, and off to bed with you. Lunchtime here (a bit later than my usual) and thanks again!

[HansV]

For what it's worth, this is what I came up with:

Code: Select all

Sub Import()
    Dim strFile As String
    Dim f As Integer
    Dim strLine As String
    Dim wsh As Worksheet
    Dim lngRow As Long
    strFile = Application.GetOpenFilename("Text files (*.txt), *.txt")
    If strFile = "False" Then
        MsgBox "No file specified!", vbExclamation
        Exit Sub
    End If
    Set wsh = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    lngRow = 0
    f = FreeFile
    Application.ScreenUpdating = False
    Open strFile For Input As #f
    Do While Not EOF(f)
        Line Input #f, strLine
        Select Case Left(strLine, 3)
            Case "+  ", "-  ", "+/-"
                lngRow = lngRow + 1
                wsh.Range("A" & lngRow).Value = strLine
                wsh.Range("A" & lngRow).TextToColumns _
                    Destination:=wsh.Range("A" & lngRow), _
                    DataType:=xlFixedWidth, _
                    FieldInfo:=Array(Array(0, 1), Array(5, 1), Array(19, 1), _
                    Array(34, 1), Array(51, 1), Array(66, 1), _
                    Array(93, 1), Array(104, 1), Array(114, 1), Array(136, 1))
            Case "Sub"
                lngRow = lngRow + 1
                wsh.Range("A" & lngRow).Value = strLine
                wsh.Range("A" & lngRow).TextToColumns _
                    Destination:=wsh.Range("A" & lngRow), _
                    DataType:=xlFixedWidth, _
                    FieldInfo:=Array(Array(0, 1), Array(88, 1), Array(89, 1), _
                    Array(90, 1), Array(91, 1), Array(92, 1), Array(93, 1), _
                    Array(104, 1), Array(114, 1), Array(136, 1))
        End Select
    Loop
    Close #f
    wsh.Range("B1:J1").EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

[Rudi]

Hi Kristi,

Between Hans's very elegant macro (importing the rows one by one) and my updated one, you should have something useful to automate this process.
As always, once you have run the macro(s), always double check to see if the data has been processed correctly and that columns line up.

Note:
I have updated my code in post 180995 to rectify the issue from last night re. the columns for Cov, Month and Carrier, and I've added a bit more cleanup and formatting.

[hooverJones]

Hi Rudi,

Sorry to reply late - I was only in the office a half day this afternoon. Thanks very much for your assistance! I will try out your updated code and check the results carefully. ;) My favorite part is reading the macro code and learning ways so I can one day write these things myself. It is really great to have such willing mentors! Lots of choccies and a beer or two!

Kristi - in Seattle Washington, US

[hooverJones]

Hi Hans, Thanks very much for your help! I am very happy to have such mentors and always learn from your examples. I will try out both your's and Rudi's macros on a nice big input file to see what pops out. Thanks again! choccies and beers for you as well!

Kristi in Seattle, Washington US

[Rudi]

Wow... I'll be bouncing after all that chocolate. TX