Concatenate cells and adding l sign and skip black cell

[westie54]

Hi

Currently I am using this formula....

=SUBSTITUTE(TRIM(D3&"^"&E3&" ^ "&F3&" ^ "&G3&" ^ "&H3&" ^ "&I3&" ^ "&J3&" ^ "&K3&" ^ "&L3&" ^ "&M3&" ^ "&N3&" ^ "&O3&" ^ "&P3&" ^ "&Q3&" ^ "&R3&" ^ "&S3&" ^ "&T3&" ^ "&U3&" ^ "&V3&" ^ "&W3&" ^ "&X3&" ^ "&Y3&" ^ "&Z3),"^", " l ")

But the issue is it substitute ^ with l so I got as a result....

l l l l Apple & Pear l l Flower & Nature l l l l l l l l l If I replace ^ with blank (" "), " l " would replace all the spaces so the result will be like below which is not what I want. Apple l & l Pear l Flower l & l Nature <tbody> </tbody>

<tbody>
</tbody>

What I am after is Apple & Pear l Flower & Nature

Can anyone advise me what formula to us please?

 

[mikerickson]

If l l l l Apple & Pear l l Flower & Nature l l l l l l l l l is in A1

=SUBSITUTE(SUBSITUTE(TRIM(SUBSITUTE(SUBSITUTE(A1," & ",CHAR(5)),"|" " "))," ","|"),CHAR(5), " & ")

 

[westie54]

Hi Mike,

The data that I want to draw data from is from column D2 to Z2. Do I need to repeat the A1," & ",CHAR(5) to get a complete formula? Sorry if my question sounds silly (this shows how little I know about excel). Thank you.

 

[mikerickson]

The formula that you are currently using:

=SUBSTITUTE(TRIM(D3&"^"&E3&" ^ "&F3&" ^ "&G3&" ^ "&H3&" ^ "&I3&" ^ "&J3&" ^ "&K3&" ^ "&L3&" ^ "&M3&" ^ "&N3&" ^ "&O3&" ^ "&P3&" ^ "&Q3&" ^ "&R3&" ^ "&S3&" ^ "&T3&" ^ "&U3&" ^ "&V3&" ^ "&W3&" ^ "&X3&" ^ "&Y3&" ^ "&Z3),"^", " l ")

I'm not sure why you are going through the carets, but to get a delimited string that preserves the existing spaces, you could replace each of those cell references with
SUBSTITUTE(D3," ",CHAR(5))
SUBSTITUTE(E3," ",CHAR(5))
SUBSTITUTE(F3," ",CHAR(5))
And use the (shortened) formula
=SUBSITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(D3," ",CHAR(5))&" "&SUBSTITUTE(E3," ",CHAR(5))&" "&SUBSTITUTE(F3," ",CHAR(5))), " ", " | "), CHAR(5), " ")

 

[mikerickson]

On the other hand, it would be much much easier to have a helper row

in D4 put the formula =D3
In E4 put the formula =IF(D4="", E3, IF(E3="", D4, D4 & " | " & E3)

Then drag right.