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

Our take

Struggling to format your data for vendor requirements? You’re not alone. Combining multiple text cells in Excel can be tedious, especially when you need to separate values with specific characters while filtering out blanks. Fortunately, there's an efficient solution. By utilizing the TEXTJOIN function in Excel 365, you can seamlessly merge text from multiple cells into one, ensuring that unnecessary gaps are filtered out. Keep reading to discover how this simple formula can save you hours of manual work and streamline your data preparation process.

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]

Read on the original site

Open the publisher's page for the full experience

View original article

Related Articles

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