+ Reply to Thread
Results 1 to 7 of 7

Concatenate, Skip Blanks and Carriage Return only after non-blank

  1. #1
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    3

    Concatenate, Skip Blanks and Carriage Return only after non-blank

    I need a formula to concatenate cells but skip the blanks and after the blanks put in a carriage return. But I need 3 carriage returns after the first cell. Here is more detail on what I have been doing. Don't laugh.

    I have tried to concatenate cells in a row and can get the formula to skip blanks or I can get it to do a carriage return (<br />) after every cell but I do not know what the formula would be to do both.
    Ultimately, this is needed for html in a csv file, which is why I need <br /> to print out but it has to be created in excel first.

    For example:

    If my cells contain the following:

    a2 = Milk and Juice. b2 = Bread, c2= Cheese, d2 = blank, e2 = blank, f2 = Bananas g2 = blank and h2 = Ice-cream

    Then my concatenate formula =CONCATENATE(a2,"<br />","<br />","<br />",b2,"<br />",C2,"<br />",d2,"<br />",e1,"<br />",f2,"<br />",g2,"<br />",h2)

    results in the following:

    Milk and Juice.<br /><br /><br />Bread<br />Cheese<br /><br /><br />Bananas<br /><br />Ice-Cream
    Which when I process it through the html ends up looking like this:

    Milk and Juice.



    Bread
    Cheese



    Bananas



    Ice-cream

    This formula:
    =SUBSTITUTE(TRIM(CE32&" "&CF32&" "&CG32&" "&CH32&" "&CI32&" "&CJ32&" "&CK32)," "," ")
    results in this:

    Milk and Juice. Bread Cheese Bananas

    I need it to look like this with 3 lines after the first and no blank lines with the remaining non-blank cells. Basically, to print out the <br /> only if it is a non-blank but 3 after the first. The first cell will always have a value in it.
    Milk and Juice.



    Bread
    Cheese
    Bananas
    Ice-cream

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Concatenate, Skip Blanks and Carriage Return only after non-blank

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

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Concatenate, Skip Blanks and Carriage Return only after non-blank

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    Please Login or Register  to view this content.
    =myConcat(Range,YourAdditionOfTextOrFormulaHere)

    =myConcat(A2:A12,REPT(CHAR(10),3))

    Change the reference A2:A12 to your desired range.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Concatenate, Skip Blanks and Carriage Return only after non-blank

    Thanks for the reply. I entered the code and the result was as follows:
    Milk and Juice.<br /><br /><br /><br /><br /><br /><br />

    I did not print out the remaining cells.

    It should look like this:

    Milk and Juice.<br /><br /><br />Bread<br />Cheese<br />Bananas<br />Ice-cream

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Concatenate, Skip Blanks and Carriage Return only after non-blank

    Quote Originally Posted by GTide View Post
    Thanks for the reply. I entered the code and the result was as follows
    Not sure to whom it is?

  6. #6
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Concatenate, Skip Blanks and Carriage Return only after non-blank

    Oops, my bad. Try this instead:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I'm not sure you need or want the carriage return after "Ice-Cream". If not simply remove the last &"<br />" from the formula above.
    Last edited by gak67; 05-14-2014 at 04:38 PM.

  7. #7
    Registered User
    Join Date
    05-13-2014
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Concatenate, Skip Blanks and Carriage Return only after non-blank

    @gak67
    That worked beautifully!
    Thank you!!

+ 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 Leave Out Blanks Add Carriage Return
    By gawk1980 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 02-03-2013, 06:36 PM
  2. Carriage Return in CONCATENATE
    By Guzziguy in forum Excel General
    Replies: 2
    Last Post: 07-10-2009, 07:14 AM
  3. Concatenate a Range Skip Blanks
    By seanyeap in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-24-2009, 08:54 PM
  4. Concatenate with carriage return- how to remove blanks?
    By ewellis in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-24-2008, 02:49 PM
  5. Concatenate - Skip Blanks
    By gquest in forum Excel General
    Replies: 1
    Last Post: 04-05-2007, 08:31 PM

Tags for this Thread

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