# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Convert Excel Column to text file seperated by commas

## stri9504

Hello,

I am receiving data in excel 2003 that I am needing to convert to a text format.

The data in excel is oriented in a single column and is over 5 thousand items in length.  I need make this data accessible in a work processor such that the items are oriented horizontally and are separated by commas.

I have attempted to utilize the special paste-transpose feature and save the file as comma delineated, unfortunately excel will not allow this as the ttl columns allowed is under 260 and it will not auto wrap onto the next row.

A condensed example below.

Excel shows:
111
123
124
134

I need it displayed:
111,123,124,134.... (over five thousand items long)

Any help would be greatly apprieciated.

Thx

----------


## martindwilson

isn't there a 256 character limit in one cell?

----------


## stri9504

Sorry for the confusion.  the column has over 5 thousand cells with individual numbers in each of them.  

Thx

----------


## martindwilson

ok you need word for this
in excel
copy column then paste special /unformatted text into word
you'll now have loads of data in a column

now select all 
use edit/replace just as excel
in find what put  ^p (this is the paragraph mark from the find special list)
in replace put   ,
then replace all

data should now change from
123
456
789
345
to 123,456,789,345,
select all /copy/paste to notepad

----------


## stri9504

Martin - Thank you very much for the assistance.  Your steps worked just as you advised they would. :Smilie:

----------


## debspecs

This helped me soooo much!  I stopped pulling my hair out, thank you.

----------


## Rovious

I have kind of a simular problem. I needed to edit a TXT file with a string of rows & columns.
For Example:
L 565.0000, 1364.0000, 28.0000,  566.0000, 1361.0000, 28.0000,  190, 190, 190
L 566.0000, 1361.0000, 28.0000,  638.0000, 1362.0000, 28.0000,  190, 190, 190

Those are just the first two lines. Using Excel's text to columns to seporate by space & commas.
That I might edit only the columns I wanted, with out having to go through the text file editting over 500 lines of text on only some lines with the values I wanted to change, while skipping ones I did not.

For Example:
L 565.0000, 1364.0000, 28.0000,  566.0000, 1361.0000, 28.0000,  190, 190, 190
L 566.0000, 1361.0000, 28.0000,  638.0000, 1362.0000, 28.0000,  190, 190, 190

L 565.0000, 1364.0000, 28.0000,  566.0000, 1361.0000, 28.0000,  0, 0, 0
L 566.0000, 1361.0000, 28.0000,  638.0000, 1362.0000, 28.0000,  0, 0, 0

My question is how would I undo the "Text to columns"? For each [space] is now a [tab] when trying to save back to text file. 
I did try the above, but it only works for the first column of cells. Ends up looking like this:

L [tab] -60.0000 [tab] -1.0000 [tab] 7.0000 [tab] -56.0000 [tab] 18.0000 [tab] 8.0000 [tab] 0 [tab] 0 [tab] 0,L[word wrap to new line] [tab] -56.0000 [tab] 18.0000 [tab] 8.0000 [tab] -46.0000 [tab] 36.0000 [tab] 7.0000 [tab] 0 [tab] 0[word wrap to new line] [tab] 0,L [tab] (ect...)
(the "spacing" does not show up exactly the same in post)

----------


## rafisch

See macro below which prompts you in the following manner

1 how many columns to the right of the existing column do you want to place the text
- do you want the content of the columns to be treated as (1) a literal (2) number
- the length of the strings (how many rows of the source column should go into a single row before a new row is started
- the delimiter between items
- the column range

Sub Transpose()
'Transpose column into multiple rows each having x elements
Dim sRow As Integer
Dim sCol As Integer
Dim eRow As Integer
Dim K As Integer
Dim j As Integer
Dim X As Integer
Dim Y As Integer
Dim strAcct As String
Dim strDelim As String
Dim Rng As Range
ActiveCell.Select
X = InputBox("Number of columns to offset", "User Input - Column Offset")
Y = InputBox("(1)-CHAR, (2)Numeric", "User Input - Output type")
Z = InputBox("Number of items per line (1 to 99)")
strDelim = InputBox("Enter delimeter", "Delimeter ?")

Set Rng = Application.InputBox("Select the range", Type:=8)


lastRow = Rng(Rng.Count).row

GoTo 1
If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        lastRow = Rng.Find(What:="*", After:=[A1], _
                           SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious).row
End If

1:

sCol = Rng.Cells(1, 1).Column
sRow = Rng.Cells(1, 1).row
K = 0       'Row Index
j = 0       'Column Index
Cells(sRow, sCol).Select

Do While ActiveCell.row < lastRow

    strAcct = " "
        Do While K <= Z - 1 And ActiveCell.row <= lastRow
            If Not ActiveCell.Value = "" Then
                If ActiveCell.row <= lastRow Then
                    If Y = 1 Then
                    strAcct = strAcct & " '" & ActiveCell.Value & "'" & strDelim
                    Else
                    strAcct = strAcct & " " & ActiveCell.Value & strDelim
                    End If
                Else
                    If Y = 1 Then
                    strAcct = strAcct & " '" & ActiveCell.Value & "'"
                    Else
                    strAcct = strAcct & " " & ActiveCell.Value
                    End If
                End If
            K = K + 1
            End If
            ActiveCell.Offset(1, 0).Select
                'Skip hidden (filtered) rows
                Do While Rows(ActiveCell.row).Hidden And ActiveCell.row <= lastRow
                ActiveCell.Offset(1, 0).Select
                Loop

        Loop

        Cells(sRow + j, sCol + X).Value = strAcct
        K = 0
        j = j + 1
Loop


End Sub

----------


## protonLeah

rafisch,
Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## protonLeah

rafisch,
_Your post does not comply with Rule 3 of our Forum_ RULES. *Use code tags around code.* 

Posting code between  [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]  tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the *#* icon at the top of your post window. More information about these and other tags can be found here



*(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)*

----------

