+ Reply to Thread
Results 1 to 5 of 5

Column of Numbers to Row in one cell

  1. #1
    Registered User
    Join Date
    07-07-2004
    Posts
    11

    Column of Numbers to Row in one cell

    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

  2. #2
    Registered User
    Join Date
    01-25-2005
    Posts
    16
    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:

    Function 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
    Press Alt+F11 to open the VB editor
    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

  3. #3
    Registered User
    Join Date
    07-07-2004
    Posts
    11

    Thanks

    I will give this a try. I certain donm't want to do each one individually...there are at least 200 numbers.

    THANKS!

  4. #4
    Registered User
    Join Date
    07-07-2004
    Posts
    11

    Problem

    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.

  5. #5
    Registered User
    Join Date
    01-25-2005
    Posts
    16
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1