0

This is regarding post: Concatenate cells but exclude empties in Excel

With the below formula, we can have data concatenated by comma's, is there a way to add CHAR(10) to have the data from the 'A,B and C" cells listed on top of one another? So if "B"is made blank it will be removed from the list?

=IF(A1="","",A1)&IF(B1="","",IF(A1<>"",", "&B1,B1))&IF(C1="","",IF(AND(A1="",B1=""),C1,", "&C1))

For Example the list would be:

A1 - Dog

B1 - "empty cell", but if data entered it would populate below and vice versa.

C1 - Cat

I am looking for a formula that would return

Dog

Cat (with no space above)

Thanks for any help!! I have been struggling with this all day and keep getting a blank for B when I make it blank.FYI, I'm formatting this in Google Sheets.

Improve this question
1
  • Are you asking how to embed a linefeed character (ASCII character 10), or 10 spaces to force line wrapping?
    – fixer1234
    Feb 2, 2018 at 4:21

1 Answer 1

Reset to default
1

By CHAR(10), I assume you mean 'add 10 spaces so that the cell wraps'. Of course, whether this causes a wrap depends on cell width and contents.

Nevertheless, the following will do as you ask:

=IF(A1="","",A1)&IF(B1="","",IF(A1<>"","           "&B1,B1))&IF(C1="","",IF(AND(A1="",B1=""),C1,"           "&C1))

All I've done is replaced the spots commas are inserted into 10 spaces. You'll have to apply wrap formatting to the cell.

--

edit

as per the comment below, if you want 9 items, I'd simplify the IF statements as follows

=IF(A1="","",A1&"          ")&IF(B1="","",B1&"          ")&IF(C1="","",C1&"          ")&IF(D1="","",D1&"          ")&IF(E1="","",E1&"          ")&IF(F1="","",F1&"          ")&IF(G1="","",G1&"          ")&IF(H1="","",H1&"          ")&IF(I1="","",I1&"          ")

You can add as many more references as you like in the same pattern.

--

edit 2

using char(10) as linefeed:

=IF(A1="","",A1&CHAR(10))&IF(B1="","",B1&CHAR(10))&IF(C1="","",C1&CHAR(10))&IF(D1="","",D1&CHAR(10))&IF(E1="","",E1&CHAR(10))&IF(F1="","",F1&CHAR(10))&IF(G1="","",G1&CHAR(10))&IF(H1="","",H1&CHAR(10))&IF(I1="","",I1&CHAR(10))

hopefully makes formatting easier

Improve this answer
5
  • Thanks, I formatted the cell and wrapped the text with the spaces. My values are long and short so this is a quick fix . It's not exact, but a manageable easy fix. Thanks again!
    – Capt. Drew
    Feb 2, 2018 at 3:54
  • Glad it helped. If this solves our problem, remember to 'accept' the answer
    – Sir Adelaide
    Feb 2, 2018 at 4:04
  • One more question, if I want to to have total of 9 possible items, on the list, how hard would it be to alter the IF formula?
    – Capt. Drew
    Feb 2, 2018 at 4:07
  • I think the question is asking how to embed char(10) (i.e., linefeed), rather than 10 spaces. But it looks like this alternate approach worked for the OP (although with unequal strings it might not).
    – fixer1234
    Feb 2, 2018 at 4:26
  • I didn't even know before you could use char(10) as a line feed in Excel... thanks
    – Sir Adelaide
    Feb 2, 2018 at 5:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .