+ Reply to Thread
Results 1 to 10 of 10

Concatenate with char(10) but skip blanks

  1. #1
    Registered User
    Join Date
    05-31-2014
    Posts
    9

    Concatenate with char(10) but skip blanks

    Hello All,

    I have a problem for which I need the solution ASAP.

    I have values in 4 cells and I want to concatenate them with char(10) but skip if they are blank and just concatenate the cells which are not blank.

    For Example,

    =CONCATENATE(S3,char(10),S4,char(10),S5,char(10),S6) this works but if S4 is blank, it will still execute char(10) after S4 and I don't want that.

    Please let me know if you have any solution to this problem.

    Thank you in advance.

    CP

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,741

    Re: Concatenate with char(10) but skip blanks

    Hi,

    It's not quite clear what you mean. You say that you won't want it to "execute CHAR(10)" if e.g. S4 is blank, which will currently look like this (in a cell with Wrap Text on):

    A

    C
    D

    So what do you want to see in this case?

    A
    C
    D

    perhaps?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    05-31-2014
    Posts
    9

    Re: Concatenate with char(10) but skip blanks

    Hi,

    Thanks for the fast reply. You got it right.

    I want it to be like

    A
    C
    D

    I will be using the concatenated value for my chart's data labels.

    Thanks again.

    CP

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,392

    Re: Concatenate with char(10) but skip blanks

    Hello,

    You could try this...

    =IF(S3<>"",S3&CHAR(10),"")&IF(S4<>"",S4&CHAR(10),"")&IF(S5<>"",S5&CHAR(10),"")&IF(S6<>"",S6&CHAR(10),"")
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  5. #5
    Registered User
    Join Date
    05-31-2014
    Posts
    9

    Re: Concatenate with char(10) but skip blanks

    Hi

    That works but it still leave char(10) at the end so it creates an empty row at the end.

  6. #6
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,392

    Re: Concatenate with char(10) but skip blanks

    =left(if(s3<>"",s3&char(10),"")&if(s4<>"",s4&char(10),"")&if(s5<>"",s5&char(10),"")&if(s6<>"",s6&char(10),""),len(if(s3<>"",s3&char(10),"")&if(s4<>"",s4&char(10),"")&if(s5<>"",s5&char(10),"")&if(s6<>"",s6&char(10),""))-1)

  7. #7
    Registered User
    Join Date
    05-31-2014
    Posts
    9

    Re: Concatenate with char(10) but skip blanks

    This works like a charm!!!!!!!

    Thank you very much...... Much appreciated.....

  8. #8
    Registered User
    Join Date
    05-31-2014
    Posts
    9

    Re: Concatenate with char(10) but skip blanks

    Hi Just another question,

    If all 4 values are blank then?

    It returns #VALUE! error.

  9. #9
    Registered User
    Join Date
    05-31-2014
    Posts
    9

    Re: Concatenate with char(10) but skip blanks

    Got the solution guys.

    =IF(OR(S3<>"",S4<>"",S5<>"",S6<>""),LEFT(IF(S3<>"",S3&CHAR(10),"")&IF(S4<>"",S4&CHAR(10),"")&IF(S5<>"",S5&CHAR(10),"")&IF(S6<>"",S6&CHAR(10),""),LEN(IF(S3<>"",S3&CHAR(10),"")&IF(S4<>"",S4&CHAR(10),"")&IF(S5<>"",S5&CHAR(10),"")&IF(S6<>"",S6&CHAR(10),""))-1),"")

  10. #10
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,392

    Re: Concatenate with char(10) but skip blanks

    You could either test for COUNTA(S3:S6)=0, or exclude the error with =IFERROR()

+ 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. Concatenate, Skip Blanks and Carriage Return only after non-blank
    By GTide in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-14-2014, 05:08 PM
  2. CHAR(10) not being recognized when using the concatenate function
    By AKM1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2013, 10: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 - Skip Blanks
    By gquest in forum Excel General
    Replies: 1
    Last Post: 04-05-2007, 08:31 PM
  5. Replies: 0
    Last Post: 02-22-2005, 12:06 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