Need Help with Lists

doh316

New Member
Joined
Jun 14, 2006
Messages
8
hi,

I was wondering how one would collapse a list of items in separate cells within a column into one cell? Is there a basic function within excel that would allow this? Please help asap. Thanks!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
You mean something like (puts spaces between values also):

=A1&" "&B1&" "&C1

Does this help?
 

doh316

New Member
Joined
Jun 14, 2006
Messages
8
I dont think thats what I need.

i have a list of items in separate cells in a column, for example

tree
dog
apples

I was wondering if there was a function in Excel that will allow me to collapse the list into a single cell separated by commas

so the result would be in cell A1:

"tree, dog, apples" in cell A1

Not sure if i need a macro for this, whatever the case may be, how would i be able to do this?

Thanks
 

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
How many cells in a column could you be looking at? Alternatively, a VBA solution could be something like:

Code:
Sub CombineValues()
'written by Barrie Davidson
Dim CombineRange As Range, c As Range
Dim NewVal As String
Dim NewValRange As Range

Set CombineRange = Application.InputBox("Select range to combine", , , , , , , 8)
For Each c In CombineRange
    NewVal = NewVal & ", " & c
Next c
NewVal = Right(NewVal, Len(NewVal) - 2)
Set NewValRange = Application.InputBox("Select range to paste combined value", , , , , , , 8)
NewValRange.Cells(1, 1) = NewVal
End Sub

Regards,
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top