+ Reply to Thread
Results 1 to 2 of 2

Concatenate single column in different rows

  1. #1
    Registered User
    Join Date
    01-15-2020
    Location
    London
    MS-Off Ver
    2010
    Posts
    1

    Concatenate single column in different rows

    Hi there!

    I am new to Excel and don't know much about it yet. I am trying to find a way I can put this data from Column A in different rows, with the text in bold first and then the rest of the info (as seen below). I've tried a formula to concatenate this, but when I do so Excel doesn't recognize that I want to separate the data when the text in bold appears again. Is there any formula or another possible way I can do this process automatically? This spreadsheet is really long.

    Unbenannt.png

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Concatenate single column in different rows

    There's no way for formulas to distinguish cells by formatting (other than some number formats) using only standard Excel functions. You could use VBA, but if you're new to Excel, that would be like having you proceed straight from single-digit arithmetic to multi-variable calculus. Even the contortions needed to be able to use number formatting would be like the jump from single-digit arithmetic to the quadratic formula.

    Given your professed newness to Excel, you'd be better off inserting a column to the left of column A (select all of column A and insert a column, Excel will insert to the left), then entering x in the new col A cells for every boldface cell in new col B which would now have your original col A values. Then add the following formulas.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Fill F1 right as far as needed. It may be easier to copy F1 (move to cell F1, press [Ctrl]+C), then select, say, G1:K1, and paste (press [Ctrl]+V).

    D2: =IFERROR(MATCH("?",INDEX(A:A,D1+1):A$1048576,0)+D1,2^20+1)

    Select E1:K1, copy, and paste into E2:K2. Select D2:K2, copy, then select D3:Kn and paste, where n is a row number from 3 on depending on how many rows of results you need. The column D formulas will return 1048577 and columns E to K formulas #REF! when you've exhausted the text data in column B.

    Yes, these are long and complex formulas. What you're doing is text processing. Excel can do it, but not easily. There are much better tools for doing things like this, e.g, most scripting languages if the source data had HTML boldface tags for the boldface lines.

    Here's a link to an example in Excel Online.
    Last edited by hrlngrv; 01-15-2020 at 08:52 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] concatenate 3 rows ,and transpose it into single column in another sheet
    By raj soni in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2016, 06:33 AM
  2. [SOLVED] How to concatenate mutiple rows and show it in a single Cell Conditionally
    By Kandavalli.Kiran in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-04-2015, 04:48 PM
  3. [SOLVED] Concatenate rows into single cell, but remove last comma from row
    By djm601 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2013, 09:45 AM
  4. macro to concatenate data from column into a single cell
    By Poonjas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-23-2012, 04:29 AM
  5. Replies: 1
    Last Post: 08-24-2012, 05:52 AM
  6. Replies: 8
    Last Post: 06-19-2012, 02:32 PM
  7. Concatenate column into single sell separated by commas
    By Onestopfanshop in forum Excel General
    Replies: 1
    Last Post: 11-16-2010, 12:01 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1