Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts

    Condense Text Running Down a Column

    Using Excel 2013 on Windows 8.1

    I have a column of cells that are text, that I've imported from a PDF file. These are responses that are broken across lines with hard returns in the PDF file. They are separated by blank rows.

    I would like to take a column of strings in A and 1) consolidate a sequence of cells back into the original statements, and 2) put them into consecutive strings in column B without the blank rows.

    For examples, my A column might look like this

    The
    quick
    brown
    fox

    The
    rain
    in
    Spain

    I'd like column B to look like this:

    The quick brown fox
    The rain in Spain

    I can do all the concatenation and spacing. What I'm having trouble with is detecting that a string has started or ended, and then putting the string into the right place.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Try this simple code:

    Code:
    Public Sub hgfghf()
    Dim LastRow As Long, I As Integer, Row As Long
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Row = 3
    strng = ""
    For I = 3 To LastRow
        If Cells(I, 1) <> "" Then
            strng = strng & " " & Cells(I, 1)
        Else:
            Cells(Row, 2) = strng
            Row = Row + 1
            strng = ""
        End If
    Next I
    End Sub
    Change Row=3 to Row=X where X is the first line that contains a fragment

    HTH,
    Maud

    strng2.png
    Last edited by Maudibe; 2015-08-12 at 21:24. Reason: added image

  3. The Following User Says Thank You to Maudibe For This Useful Post:

    boobounder (2015-08-13)

  4. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    This is almost perfect.

    It doesn't convert the last sequence of strings in column A.

    As a corollary, when there is no break in the sequence at all, it returns nothing in column B.

  5. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Add this before the Else.
    If I = LastRow Then
    Cells(Row, 2) = strng
    End If

    cheers, Paul

  6. The Following User Says Thank You to Paul T For This Useful Post:

    boobounder (2015-08-13)

  7. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    Yes, that works now. Thanks. It's not that general, but I was able to figure out which parts to change if my data is organized differently.

    I have a related question that I think can stay in this thread.

    Honestly, if I have to do looping with even simple data structures like this ... I don't use Excel.

    So, what I wonder is, if I want to stay in the spreadsheet and use just functions and formulas, is there a way to condense the column after concatenating the strings.

    For example, it is easy to start with this in the A column:

    a
    b

    c
    d
    e

    And to do some "cumulative concatenation" in the B column to get:

    a a
    b ab

    c c
    d cd
    e cde

    But how would I go down column B and take each string that's before the space and collect them in column C with no spaces, so that it looks like this:

    a a ab
    b ab cde

    c c
    d cd
    e cde

  8. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Not sure I get what you want. How did you get this line, "b ab cde"?

    cheers, Paul

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Boo,

    I think this will do it. It will put your cumulatives in column C. Paul, thanks for your tweak!

    Code:
    Public Sub hgfghf()
    Dim LastRow As Long, I As Integer, Row As Long
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Row = 3
    strng = ""
    For I = 3 To LastRow
        If Cells(I, 1) <> "" Then
            strng = strng & " " & Cells(I, 1)
            Cells(I, 3) = strng
            If I = LastRow Then
                Cells(Row, 2) = strng
            End If
        Else:
            Cells(Row, 2) = strng
            Row = Row + 1
            strng = ""
        End If
    Next I
    End Sub
    abc.png

  10. #8
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    Now I'm sad Maudibe ...

    Your VBA worked just fine for me after post # 4.

    That's why I said I have a slightly different question in post # 5.

    I am trying to figure out how to do what your VBA code does ... without the looping code and the data structures.

    I know that sounds dumb. But I get questions like this a lot (since I'm what passes for an expert around where I work), and solutions that use VBA intimidate most people. So I show them how to do things with functions and formulas in cells.

    So my approach to solving this problem for a user (before coming to the lounge) was not to use VBA. I think your solution is great if it's just for me. But if I have to pass that off to a novice, I'll bet you that they roll their eyes and go back to doing it by hand.

    In doing this by hand, I had no trouble getting from column A to column C with just functions and formulas in cells. I can even identify the ends of the strings in C6, C10, C13, and C18.

    I only had trouble on what would then be the next step: once I've isolated those 4 cells I want, how do I get Excel (using nothing but formulas and functions in B3.B6) to show them in B3.B6 without blank rows.

  11. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 986 Times in 916 Posts
    There are some things you just can't do with formulas and I suspect this is one of them.

    cheers, Paul

  12. The Following User Says Thank You to Paul T For This Useful Post:

    boobounder (2015-08-15)

  13. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Would it be acceptable to convert this to a UDF? So, in cell B2 they might put a formula like:
    =AddStrings(A2:A6)

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    boobounder (2015-08-15)

  15. #11
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    Paul T: That's a great answer. I'm still open to suggestions. I just don't feel qualified enough to assert on my own that this can't be done.

    Maudibe: (I'm assuming you mean User Defined Function). I think that would run into the same trouble. People kind'of draw these lines that they won't cross: I don't make them up, I just figure out how to roll with them.

    I'm content to end the thread if you guys have no other suggestions.

  16. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Boo,

    I have a couple of ideas for a non vba method. Hold off for a bit.

    Maud

  17. The Following User Says Thank You to Maudibe For This Useful Post:

    boobounder (2015-08-16)

  18. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Boo,

    I was able to do this with a helper column F which you can hide if you prefer. Column F checks to see if its adjacent cell in column A is blank. If it is not then it places a "*" in the F cell. If it is blank, it places the row number. The formula is:

    In cell F3: =IF(ISBLANK(A3),ROW(),"*") then copy down

    The formula in column B looks at the adjacent value in column J. If it is "*" then it concatenates the value above it (B col) with the value in column A to its left. If the adjacent value in column J is not "*" then the cell returns a blank. The formula is:

    In cell B3: =IF(F3<>"*","",IF(F3="*",B2 & A3,"*")) then copy down

    On caveat, cell B2 must be blank as it is used in the concatenation.

    HTH,
    Maud

    boo1.png
    Attached Files Attached Files

  19. The Following User Says Thank You to Maudibe For This Useful Post:

    boobounder (2015-08-16)

  20. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Boo,

    You can do this without the helper column F. in B3, enter the following formula then copy down:

    =IF(A3="","",B2 & A3)

    Maud

    note: cell B2 still needs to be blank

  21. The Following User Says Thank You to Maudibe For This Useful Post:

    boobounder (2015-08-16)

  22. #15
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    412
    Thanks
    39
    Thanked 5 Times in 5 Posts
    Thanks for helping me over the hump. I think the attached file does what I want. I use a lot more "helper" columns. It's cumbersome, but I think it does the trick of keeping everything out in the open.
    Attached Files Attached Files

  23. The Following User Says Thank You to boobounder For This Useful Post:

    Maudibe (2015-08-16)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •