2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

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.

submitted by /u/Fun-Onion4302
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#Excel alternatives for data analysis
#AI formula generation techniques
#Excel compatibility
#financial modeling with spreadsheets
#natural language processing for spreadsheets
#generative AI for data analysis
#real-time data collaboration
#rows.com
#Excel
#TEXTJOIN
#formulas
#text cells
#combine
#Excel 365
#format
#vendor
#separator
#multiple text cells
#final column
#aggregating text