Is it possible to combine cells in one 'Outcome Cell' while simultaneously omitting any cell that is blank?
Below is an image of the desired outcome. Currently I'm having to use =CONCATENATE to get this outcome but then I have to go through it to remove all of the blank cells manually (with over 50 columns and 1000 rows this takes a long time).
Also is a link to the spreadsheet for you to see:
You can try this:
IF(NOT(ISEMPTY(A2)), A$1 & ": " & A2 & CHAR(10), "") & IF(…
EDIT:
=IF(NOT(ISBLANK(A2)), A$1 & ": " & A2 & CHAR(10), "") &
IF(NOT(ISBLANK(B2)), B$1 & ": " & B2 & CHAR(10), "") &
IF(NOT(ISBLANK(C2)), C$1 & ": " & C2 & CHAR(10), "") &
IF(NOT(ISBLANK(D2)), D$1 & ": " & D2, "")
All in one line. ISEMPTY is the VBA variant of the worksheet function ISBLANK.
Use the formula
=CONCATENATE(IF(A3="","","Size: "&A3&CHAR(10)),IF(B3="","","Material: "&B3&CHAR(10)),IF(C3="","","Colour: "&CHAR(10)),IF(D3="","","Weight: "&D3))
Note: make sure you have text wrap enabled on cells that contain line breaks.
This formula should help you:
=CONCATENATE(IF(NOT(ISBLANK(A2));CONCATENATE($A$1;":";" ";A2);"");CHAR(10);IF(NOT(ISBLANK(B2));CONCATENATE($B$1;":";" ";B2);"");CHAR(10);IF(NOT(ISBLANK(C2));CONCATENATE($C$1;":";" ";C2);"");CHAR(10);IF(NOT(ISBLANK(D2));CONCATENATE($D$1;":";" ";D2);""))
Take a look in my spreedsheet: Excel
Use TEXTJOIN:
=TEXTJOIN(CHAR(10),TRUE,IF(A2:D2<>"",$A$1:$D$1&": "&A2:D2,""))
Depending on one's version this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.
