+ Reply to Thread
Results 1 to 10 of 10

Convert Excel Column to text file seperated by commas

  1. #1
    Registered User
    Join Date
    04-15-2008
    Posts
    3

    Convert Excel Column to text file seperated by commas

    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

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    isn't there a 256 character limit in one cell?

  3. #3
    Registered User
    Join Date
    04-15-2008
    Posts
    3

    There are over 5 thousand cells in once column.

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

    Thx

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    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

  5. #5
    Registered User
    Join Date
    04-15-2008
    Posts
    3

    Thumbs up Many thanks - worked like a charm!

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

  6. #6
    Registered User
    Join Date
    02-09-2007
    Posts
    12
    This helped me soooo much! I stopped pulling my hair out, thank you.

  7. #7
    Registered User
    Join Date
    02-07-2012
    Location
    Raleigh,NC
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Convert Excel Column to text file seperated by commas

    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)

  8. #8
    Registered User
    Join Date
    06-06-2006
    Posts
    1

    Re: Convert Excel Column to text file seperated by commas

    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

  9. #9
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Convert Excel Column to text file seperated by commas

    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.
    Ben Van Johnson

  10. #10
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Convert Excel Column to text file seperated by commas

    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)

+ 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