Is there a statement to take a column of numbers like this:
41922
41877
410032
410001
41718
and convert it to this in a single cell:
(41922,41877,410032,410001,41718)
THANKS!
Terry
Is there a statement to take a column of numbers like this:
41922
41877
410032
410001
41718
and convert it to this in a single cell:
(41922,41877,410032,410001,41718)
THANKS!
Terry
The CONCATENATE function will work if you want to click on each cell and add in a separator between each value. Kind of clunky IMO, though.
A better solution is a User Defined Function (UDF) such as:
Press Alt+F11 to open the VB editorFunction RangeConCat(InpRange As Range, Optional Sep As String)
'InpRange is the range you wish to concatenate
'Sep is the optional separator to use
For Each Cell In InpRange
'Use only non-blank cells
If Not Cell = "" Then RangeConCat = RangeConCat & Cell & Sep
Next Cell
'Trim the extra separator from the string
RangeConCat = Left(RangeConCat, Len(RangeConCat) - Len(Sep))
End Function
Create a new module - Insert|New Module
Copy the code from above and paste it into the module
Close the VB editor
Click in the cell you want your formula in
Click on the Paste Function button (looks like a fancy lower case f followed by an x)
Select User Defined from the list on the left (it should be at the bottom of the list)
Select RangeConCat from the list on the right
The formula composer will display, allowing you to select the range you want to concatenate and enter the character you want use for a separator (optional)
Click on OK
I will give this a try. I certain donm't want to do each one individually...there are at least 200 numbers.
THANKS!
I am using Excel 2002, does that make any difference?
I copied and pasted the code into a new module. It acts like it stops before it is finished. When I selected the function from user defined, I set the range, and a comma as a separator and clicked okay. It shoes me the actual formula in the cell where I want the results, and the range I selected still has the bluw slection box around it. What is displaying in the cell is =RangeConCat(a2:a9,","). It won't do anything after that.
Hmmm.... I'm using XL 2000, so that may make a difference. Have you tried using a different separator? I realize that the end result you're after may require the comma, but there's a chance that the comma is what is causing the problem.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks