I need to combine cells C2, D2, E2, F2 into one cell and then separate each cell content with a comma. Then I have to be able to use the fill handle to drag down for the other rows. Can someone help me with this please? I know how to CONCATENATE with 2 cells, but it's not working for me with a range of cells.
ANY help would be appreciated.
Karen
Last edited by Karen615; 09-11-2013 at 05:31 PM.
Please Login or Register to view this content.
Regards
Sean
Please add to my reputation if you think i helped (click on the star below the post)
Mark threads as "Solved" if you have your answer (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [code]
Please supply a workbook containing example Data: It makes its easier to answer your problem & saves time!
Thank you!
There are some cells that I have nothing to CONCATENATE and this formula returns ,,,
Then when there are only 2 cells with data, it returns ABC,XYZ,,
Is there a way to place a space in between each cell data and then remove the unnecessary commas?
Thanks again for your help.
Karen
=IF(RIGHT(IF(C2="","",C2&",")&IF(D2="","",D2&",")&IF(E2="","",E2&",")&IF(F2="","",F2),1)=",",LEFT(IF(C2="","",C2&",")&IF(D2="","",D2&",")&IF(E2="","",E2&",")&IF(F2="","",F2),LEN(IF(C2="","",C2&",")&IF(D2="","",D2&",")&IF(E2="","",E2&",")&IF(F2="","",F2))-1),IF(C2="","",C2&",")&IF(D2="","",D2&",")&IF(E2="","",E2&",")&IF(F2="","",F2))
Long winded way to do this and i'm sure there is something better out there but this will cover for all situations with your text join (avoids multiple commas and removes a starting and finishing comma).
Say thanks, click *
VERY Fancy! Thank you! It works great!
Have a great day!
or this...
=SUBSTITUTE(C2&","&D2&","&E2&","&F2,",,",", ")
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Try this...
Data Range
This formula entered in G2:
=SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2)," ",", ")
Copy down as needed.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Using the sample data in my other reply your formula returns:Originally Posted by FDibbins
,Data1, Data2
Your formula fail if there are more than one word in a cell.
The OP didn't say the data was TEXT so we don't know if there might be more than one word in a cell.
Well, the OP did say this:The OP didn't say the data was TEXT so we don't know if there might be more than one word in a cell.
I don't see any multiple word entries, do you?Then when there are only 2 cells with data, it returns ABC,XYZ,,
LOL you arguing with yourself now Biff? (who won?)
Just reviewing the facts as we know them!
Almost any formula will fail if you try hard enough!Your formula fail[s]
I have done with VBA (Not invented by me)
Please try this file.
Please Login or Register to view this content.
Last edited by ramananhrm; 09-13-2013 at 02:37 AM.
Please click 'Add reputation', if my answer helped you.
Thank you for your help!
There are multiple word entries! I just didn't add the hundreds of rows to my example of data.
Thanks
Try this...
Data Range
This formula entered in G2:
=TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(C2&","&D2&","&E2&","&F2," ","~"),","," "))," ",", "),"~"," "))
If you have "many" cells to concatenate (per row) then I would suggest using the UDF located here:
http://www.excelforum.com/showthread...=1#post3096647
Last edited by Tony Valko; 09-12-2013 at 10:08 AM.
Do you have to enter every cell needed or is there a quicker way to have the formula continue for like 36 rows of info?
How about starting your own thread. It's against the formula rules to "hijack" someone else's thread and start asking your own questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks