how can i write a formula to grab all cells within a selected range and combine them into one cell separated by commas?
At small volumes i was just using =A1&","&" "&A2..... but now i need to quickly and flexibly grab any range (some of my lists will be 5 rows needing to be combined into a single cell, others will be 100 rows) and combine all the values into a single cell.
Can someone please suggest how I can set this up?
There is no native formula to do that in Excel. I recommend a udf. Here's one from Harlan GroveSo after putting the code into a module
Please Login or Register to view this content.
=aconcat(A1:B2,",") would give the contents of A1,B1,A2,B2
Does that work for you?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
a udf? "after putting that code into a module"?
Sounds to me like this is working with vba? I've never worked with that before, but I'm open to trying. How do i get started to enter this code?
Yes, this is VBA and UDF stands for User Defined Function.
ALT + F11 opens the VBA editor. Insert> Module and paste the code into the text window that opens up.
Then close the editor (no need to save)
Now in your workbook, give it a try
=aconcat(A1:B2,",") or whatever your range is. If you want a space after the comma
=aconcat(A1:B2," ,")
Let us know if you have problems.
didn't get back to this project until today. Worked flawlessly. Thank you.
Now you're using UDF's. You're officially an Excel Wizard.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks