Combining Multiple Text Cell with Added Characters and Filtering Out Blanks
Our take
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]
- Combining texts in rows with a common value into a single row separated by commasHello, in my Excel file I have the products sold on invoices, but these products are listed separately in different rows. For example, if an invoice contains 5 products, they are written in 5 separate rows in Excel. What I need is to combine only the product names into a single cell (or column) on another sheet. I don’t know how to do this and I’ve been struggling with it for a long time. Could you please help me? https://preview.redd.it/uimcs81ds5vg1.png?width=293&format=png&auto=webp&s=dd5b74a78012f949b45e9288daa23238e229210e This is how it looks like https://preview.redd.it/nfljte9ls5vg1.png?width=386&format=png&auto=webp&s=c6dc4eb7f5a2393b340feee3947958e4eaf9c80a I need like this submitted by /u/afluenza23 [link] [comments]
- How to sum a conditional formula across multiple cells?Hi, I'm looking for an elegant solution to this problem. Mostly just a curiosity because I currently do have it working, but I know there is a cleaner way to do this. Basically I am trying to sum the number values from a row of 5 cells, where the cell may or may not start with a letter (i.e., the row could be "8, 9, S6.5, 8, A6" and I want the result of 8+9+6.5+8+6=37.5). I have many rows of this, and the summed value ends up in the column to the right of the 5 values. I've managed to do this with the following formula, where the result is in cell J5: =IF(ISTEXT(E5), RIGHT(E5, LEN(E5)-1), E5) + IF(ISTEXT(F5), RIGHT(F5, LEN(F5)-1), F5) + IF(ISTEXT(G5), RIGHT(G5, LEN(G5)-1), G5) + IF(ISTEXT(H5), RIGHT(H5, LEN(H5)-1), H5) + IF(ISTEXT(I5), RIGHT(I5, LEN(I5)-1), I5) But, if I recall from years ago, there is a way to make the formula act on an array of cells using ctrl+shift+enter so the formula would essentially look like this, and do the same thing: ={SUM(IF(ISTEXT(E5:I5), RIGHT(E5:I5, LEN(E5:I5)-1), E5:I5))} Thing is, it doesn't seem to cooperate, and I have gotten pretty rusty with excel. This array formula will sum values but not if there is text in the cell. Example data: M T W Th F SUM T8 T8 9 9 5 39 9 8 8 A8 8 41 8 8 8 8 8 40 S8 9.5 8 8 6 39.5 submitted by /u/ganduvo [link] [comments]
- Excel seems to think cell isn't big enough so 2 digit number looks crazy, no adjustment fixes issueI have multiple sheets that add together to make a total on the first page. Easy. The numbers are small - 3, 5, 10, nothing big. On the totals sheet, all the two digit sums are wrapped so that they are very small. I do not have wrapped text on but when I DO wrap the text, it stops wrapping them BUT there's this giant space between the two digits like "1 0" for 10. I tried everything I could think of - rewriting the formula as a SUM function (rather than this sheet + this sheet + this sheet), making sure each cell is formatted as a number, running "text to column" on each column of data, switching the font, switching the font size, changing the column size manually, double clicking on the columns to have them auto size, turning off shrink to fit, turning on shrink to fit again after, playing with the alignment.... Does anyone know what is going on and how I can solve it? Adding images (w/ some edits for privacy for my company). Will include the formulas (both versions). https://preview.redd.it/6n05pqeer4og1.png?width=1654&format=png&auto=webp&s=64729e1558bfba595d7d043b18d777b3e33e4ecf https://preview.redd.it/ydydstbfr4og1.png?width=1664&format=png&auto=webp&s=27e3d7cd18c2c380a5cf1a48853060a4bcd17c7c https://preview.redd.it/7dcraf9gr4og1.png?width=1106&format=png&auto=webp&s=4c93c635fbc20762a18cc8e137b6a40ee37c470e submitted by /u/catincombatboots [link] [comments]