Combining "standard" strings of text in one cell, if any text in cells.
I have a "comments" cell, which should contain a "standard" text phrase, if there are any values in certain cells. About 10 cells must be connected to this cell. The 10 cells are all containing numbers, and for each cell, if there is a number, a text must be come up in the "comments" cell. If there are multiple cells with numbers, the different text strings should be separated by a comma. I have attached an example sheet.
Last edited by Allerdrengen; 10-25-2018 at 07:25 AM.
Hi,
Your file hasn't been attached. The paperclip sadly doesn't work.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Ferdy
Remember to mark as Solved and give out rep.
Hi,
Thanks, my bad!
What is the text that should be coming up, as we just see the numbers atm, we can only connect those in the comment cell. We don't really know what you want in the comments field, does it pull the comments from somewhere, or are there standard phrases for each column.
There are standard phrases for each column. So each time there is a number in "Cell 2" for instance, the same text string should show in the related "comments" cell.
So in this example there should be 10 different text strings. I'm not sure whether to pull them from somewhere or if there is a better way.
Thanks!
Please try at K2 with Ctrl+Shift+Enter then copy down
=textjoin(", ",,IF(A2:J2<>"","Cell " &COLUMN(A2:J2)&" text string",""))
Thanks for your response!
The problem in that is that all the strings are completely different, and this would give me almost the same strings. The strings are not related to what is written in the cells.
In the sample file all of the text strings are almost the same, except for the column number reference. If that isn't what you want the comment cell to display, then please upload another file showing what you expect to see based on the content of the cells in columns A:J
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Yes! A more accurate file is now uploaded.
Thank you.
It may be that the TEXTJOIN function would do this more efficiently, however I can't use it as I only have the 2010 version of Excel, so here is a lengthy formula that yields the comments shown in the file:Formula:
Please Login or Register to view this content.
Note that each 'Statement' is found in the appropriate cell in row 9 and could be moved and/or hidden for aesthetic purposes.
Let us know if you have any questions.
Please try at K2 with Ctrl+Shift+Enter
textjoin for display text from row1, you need to add full text at row1 or as JeteMc suggest, add helper row with full text and change $A$1:$J$1 to that row
=textjoin(", ",,IF(A2:J2<>"",($A$1:$J$1),""))
Thank you very much, it almost does the job. However, i failed to mention that the numbers appearing in the cells are from functions in the cells. So the textjoin method thinks there a values in the cells, even though its just functions. Is there a way, to tell the textjoin function that it should only join the text, if there a numbers appearing in the cell?
Nevermind, figured it out! Thank you both, repped.
How about
=textjoin(", ",,IF(ISNUMBER(A2:J2),($A$1:$J$1),""))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks