How do I combine the contents of multiple cells in one cell?
I am putting together a form in Excel 2003 where several options can be
selected, and I want to combine the information in the selected cells in another cell with commas seperating the information. For example, I have one column with environmental hazards listed, one in each row (storage tanks, batteries, generaters), and in the next column the person filling out the form can select yes or no if that hazard is present. I want to take the information from the cells next to the cells that are marked yes, and put it together, separated with commas, in another cell in a different worksheet . If someone could please help, I would sure appreciate it! |
How do I combine the contents of multiple cells in one cell?
If the list of items isn't too many, this formulas play may suffice ..
Sample construct available at: http://cjoint.com/?mqhAvzNhVX CombineMultipleCellContentsInOneCell_Debbie_wks.xl s In Sheet: Q, Assume the list of items is in A2:A10 (a short list), with B2:B10 containing a data validation* droplist, e.g.: *created via Data Validation: with settings: under "Allow:" List, Source: Yes,No EnvHazards CheckList Storage tanks Yes Batteries No Generators Yes etc Put in C2, copy down to C10: =IF(A2="","",IF(B2="Yes",SUBSTITUTE(TRIM(A2)," ","-"),"")) Then in Sheet: A, Put in A2: =SUBSTITUTE(TRIM(Q!C2&" "&Q!C3&" "&Q!C4&" "&Q!C5&" "&Q!C6&" "&Q!C7&" "&Q!C8&" "&Q!C9&" "&Q!C10)," ",", ") A2 will return the desired results** **any item with 1 word will appear hyphenated For the sample above, we'd get in A2: Storage-tanks, Generators Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Debbie" wrote in message ... I am putting together a form in Excel 2003 where several options can be selected, and I want to combine the information in the selected cells in another cell with commas seperating the information. For example, I have one column with environmental hazards listed, one in each row (storage tanks, batteries, generaters), and in the next column the person filling out the form can select yes or no if that hazard is present. I want to take the information from the cells next to the cells that are marked yes, and put it together, separated with commas, in another cell in a different worksheet .. If someone could please help, I would sure appreciate it! |
How do I combine the contents of multiple cells in one cell?
Thank you!!! That worked great. :o)
"Max" wrote: If the list of items isn't too many, this formulas play may suffice .. Sample construct available at: http://cjoint.com/?mqhAvzNhVX CombineMultipleCellContentsInOneCell_Debbie_wks.xl s In Sheet: Q, Assume the list of items is in A2:A10 (a short list), with B2:B10 containing a data validation* droplist, e.g.: *created via Data Validation: with settings: under "Allow:" List, Source: Yes,No EnvHazards CheckList Storage tanks Yes Batteries No Generators Yes etc Put in C2, copy down to C10: =IF(A2="","",IF(B2="Yes",SUBSTITUTE(TRIM(A2)," ","-"),"")) Then in Sheet: A, Put in A2: =SUBSTITUTE(TRIM(Q!C2&" "&Q!C3&" "&Q!C4&" "&Q!C5&" "&Q!C6&" "&Q!C7&" "&Q!C8&" "&Q!C9&" "&Q!C10)," ",", ") A2 will return the desired results** **any item with 1 word will appear hyphenated For the sample above, we'd get in A2: Storage-tanks, Generators Adapt to suit .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Debbie" wrote in message ... I am putting together a form in Excel 2003 where several options can be selected, and I want to combine the information in the selected cells in another cell with commas seperating the information. For example, I have one column with environmental hazards listed, one in each row (storage tanks, batteries, generaters), and in the next column the person filling out the form can select yes or no if that hazard is present. I want to take the information from the cells next to the cells that are marked yes, and put it together, separated with commas, in another cell in a different worksheet .. If someone could please help, I would sure appreciate it! |
How do I combine the contents of multiple cells in one cell?
You're welcome !
Thanks for the feedback .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Debbie" wrote in message ... Thank you!!! That worked great. :o) |
All times are GMT +1. The time now is 09:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com