Share via

Reverse Text to Columns ...

Anonymous
2012-03-29T15:52:19+00:00

Excel 2003 ... Often when importing data to Excel ALL data ends up in a single cell with some sort of delimiter ... Then we use "Text to Columns" to parse the data into individual cells ...

Now ... Is there a way I can go from a list of entries down a Column ... Back into a single line list in a single cell with delimiters?

Issue ... we have list of items in Excel ... Which we would like to populate into a different program that requires the single line format seperated by a semi-colon.

Have Excel  Col (each number followed by a semi-colon & space)

0000001234

0000001123

0000004564

0000005678

Need in a single cell ...

0000001234; 0000001123; 0000004564; 0000005678

I would like to handle up to 100 entries (or 1000) ... What ever is possible (if possible)?

I have been using the "&", but this is cumbersome with a few hundred records and a limit of 30 "&"s.

Thanks ... Kha

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-03-29T16:12:09+00:00

    Here is a non-looping UDF (user defined function) that will output what you asked for...

    Function Col2Cell(R As Range) As String

      Col2Cell = Join(Application.Transpose(R.Value), "; ")

    End Function

    Simply pass the column range that you want to join together as the argument. For example, if you wanted to join together the values in A3:A10, you would put this formula in the cell you wanted the output to go to...

    =Col2Cell(A3:A10)

    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function.

    5 people found this answer helpful.
    0 comments

15 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2012-04-02T23:44:14+00:00

    Hi,

    Try this

    1. Supose your data is in range A2:A100
    2. In cell B2, enter =A2
    3. In cell B3, enter =B2&"; "&A3
    4. Copy cell B3 down till B100

    You desired answer should be in cell B100.

    Hope this helps.

    9 people found this answer helpful.
    0 comments
  2. Anonymous
    2012-07-02T20:15:32+00:00

    I did text to columns to sort a large list of addresses, but wanted to get the full address back into one cell.  Here's the simple formula for columns to cell, with no need for code:

    =A1&" "&B1&" "&C1&" "&D1&"   (write the formula in E1)

    You may need to convert all cells to text first, especially for addresses.

    Simply replicate the formula down the column and voila!

    A                  B                  C                       D                               E

    1863 NW 29 ST 1863 NW 29 ST
    1864 NW 29 ST 1864 NW 29 ST
    1864-68 NW 29 ST 1864-68 NW 29 ST

    4 people found this answer helpful.
    0 comments
  3. Anonymous
    2012-03-29T16:09:11+00:00

    Kha,

    Enter this into a standard codemodule in your workbook:

    Function Concat(r As Range, d As String) As String

    Dim i As Integer

    Concat = r.Cells(1).Value

    For i = 2 To r.Cells.Count

    Concat = Concat & d & r.Cells(i).Value

    Next i

    End Function

    And then use it like this in a cell:

    =Concat(C2:C1000,"; ")

    In this case, the delimiter d is "semicolon space" not sure if that is what you actually need or not.

    You may be able to create strings up to 2 billion characters if you export directly to a file using code only - I think there is a limit of 32K on entry into a cell for 2003, but maybe more for 2007 - but  I've been wrong before.

    Bernie

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2012-03-29T16:28:29+00:00

    Hi,

    You may use VBA Macro to do this...let say you Range is in A1:A100, then the concatenated value will merge to B1

    Press F11 | Insert Module in Project Window | then Paste this formula...

    Sub concat()

    Dim Rng As Range, cell As Range

    Dim concatvals As String

    Set Rng = Range("a1", Range("a65536").End(xlUp))

        For Each cell In Rng

            concatvals = concatvals & cell.Value & "; "

        Next cell

        Range("b1").Value = concatvals

    End Sub

    There's a limitation of this code,  due to the Cell itselt, it can only handle max lengt up to 32,767 characters. If exceed, it only show the value in first row. But you said, your row count is only up to 100 so no worry. ☺

    Good Luck!

    ~jaeson

    0 comments