Combining Multiple Text Cell with Added Characters and Filtering Out Blanks
I use the file on the regular, but our vendor requires it in a specific format and that format can take a lot of time for the final column.
Basically, I have anywhere from 1 to 12 text cells I need to combine and if there's another cell with text afterwards, they need to have "|" there to separate them.
Let's say I'm looking to have A1 basically equal the text B1+ if C1 is not blank then +"|"& text of C1, then + if D1 is not blank then +"|"& text of D1 and so on until blank.
I think I should have something like the below when done in the file
A1=B1text|C1text|D1text|E1text
A2=B2text|C2text|D2text
A3=B3text
A4=B4text|C4text|D4text|E4text|F4text|G4text
and so on down the file
I'm pretty familiar with excel and if formulas (not pivot tables though) but I just can't seem to make something work that still does not add a bunch of work afterwards. The closest in the past I came was something like the below but still ended up doing it manually in the end.
A1=B1text|C1text|D1text|E1text||
A2=B2text|C2text|D2text|||
A3=B3text|||||
A4=B4text|C4text|D4text|E4text|F4text|G4text
Any help would be very much appreciated.
Please note that this is Excel 365.
Edit1 : As multiple people have stated and solved this, my final formula has been as follows, =TEXTJOIN("|", TRUE, B1:M1). When I entered this the first time I noticed that the cell did not execute the formula and looked really close to the formula I had there currently (I just missed the |) and discovered that that column was formatted as text and once I changed it to general the formula executed properly. Thank you again for those of you that answered and will save me day of my life on this in the future.
[link] [comments]
Want to read more?
Check out the full article on the original site