IFBLANK formula to output only certain columns and specific texts

[Isara]

I have a number of columns, all relating to the dimensions of art objects. Unfortunately, each column relates to a different type of measurement:

N1 Diameter
P1 Height (centimeters)
Q1 Height (feet)
R1 Height (inches)
S1 Length (centimeters)
T1 Length (feet)
U1 Length (inches)
V1 Width (centimeters)
W1 Width (feet)
X1 Width (inches)

What I want the output to be is a dimensions column that has the values formatted like the following examples:

287.02 cm (l); 10.795 cm (w)
10.5 in (h); 10.5 in (l); 10.5 in (w)
177.79 cm (dia); 78.73 cm (l)

I was trying to repurpose this other formula to my needs, but I can't quite wrap my head around the way the formula needs to be structured. I just keep getting errors when I try to modify it.

=IF(ISBLANK(N2),"",", "&N2)&IF(ISBLANK(P2),"",", "&P2)&IF(ISBLANK(Q2),"",", "&Q2)&IF(ISBLANK(R2),"",", "&R2)&IF(ISBLANK(S2),"",", "&S2)&IF(ISBLANK(T2),"",", "&T2)&IF(ISBLANK(U2),"",", "&U2)&IF(ISBLANK(V2),"",", "&V2)&IF(ISBLANK(W2),"",", "&W2)&IF(ISBLANK(X2),"",", "&X2)

You guys are amazing, so I hope your'e able to help with this. Thanks in advance.

excel-book-formula.xlsx

[martindwilson]

=SUBSTITUTE(TRIM(N2&" "&P2&" "&Q2&" "&R2&" "&S2&" "&T2&" "&U2&" "&V2&" "&W2&" "&X2)," ",";") will give the values

[Isara]

Thanks so much for your quick reply. I do need to have the text bits in there, though (cm, in, (l), (w), etc.). The values of the cells are just numbers, with no way to denote what the values mean if I condense them into one cell.

[martindwilson]

=SUBSTITUTE(SUBSTITUTE(TRIM(IF(N2="","",N2&"^cm^(dia)")&" "&IF(P2="","",P2&"^cm^(h)")&" "&IF(Q2="","",Q2&"^ft^(h)")&" "&IF(R2="","",R2&"^in^(h)")&" "&IF(S2="","",S2&"^cm^(h)")&" "&IF(T2="","",T2&"^ft^(l)")&" "&IF(U2="","",U2&"^in^(l)")&" "&IF(V2="","",V2&"^cm^(w)")&" "&IF(W2="","",W2&"^ft^(w)")&" "&IF(X2="","",X2&"^in^(w)"))," ",";"),"^"," ")
gives for example

10.5 in (h);10.5 in (l);10.5 in (w)

[Isara]

oh. wow. there is absolutely no way I would have been able to figure that out. thank you so much!

Is there some handy, digestible, guide somewhere that explains what I'm looking at?

[martindwilson]

its quite simple really assuming b1 is blank
a1&" "&b1&" "&c1 gives a1 space space c1 put trim around it it gets rid of extra spaces
trim(a1&" "&b1&" "&c1)
gives
a1 space c1
so then substitute the space for ;
substitute(trim(a1&" "&b1&" "&c1)," ",";")
gives a1;b1
but you want to add some text so replace a1 with
so a1&"sometexta"&" "&b1&"sometextb&" "&c1&"sometextc
but if b is blank you'd get
a1sometexta" "sometextb" "c1sometextc
so use an "if" on each cell to return blank if its empty
if(a1="","",a1&"sometexta") do the same for each cell in the formula
trim(a1&" "&b1&" "&c1)
now because you want a1 space sometexta space somemoretexta
eg a1 x y if you used the substitute on the result as it stands it would change it to
a1;x;y
so instead of=if(a1="","",a1&"sometexta")
if(a1="","",a1&"^sometexta"&"^somemoretexta) repeat for each cell
this ends up as
a1^sometexta^somemoretexta
so after first substituting space with;
the original with trim would result in
a1^sometexta^somemoretexta;c1^sometextc^somemoretextc
so to put the spaces back in substitute ^ with " "
substitute(a1^sometexta^somemoretexta;c1^sometextc^somemoretextc,"^"," ")
finally giving
a1 sometexta somemoretexta;c1 sometextc somemoretextc