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.

  • Are you asking how to embed a linefeed character (ASCII character 10), or 10 spaces to force line wrapping? – fixer1234 Feb 2 '18 at 4:21
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

  • 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 '18 at 3:54
  • Glad it helped. If this solves our problem, remember to 'accept' the answer – Sir Adelaide Feb 2 '18 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 '18 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 '18 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 '18 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.