Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a long, comma-separated list which looks like this in excel:

401.50,0.027  
402.00,0.028  
402.50,0.029  
403.00,0.031  
403.50,0.032  
404.00,0.034  
404.50,0.037  

Is there a simple way to convert this into two separate columns? There are over 800 values and I am really not looking forward to separating them all individually.

share|improve this question
add comment

3 Answers

Have you tried using Text to Columns?

  1. Highlight the column that contains your list.
  2. Go to Data > Text to Columns.
  3. Choose Delimited. Click Next.
  4. Choose Comma. Click Next.
  5. Choose General or Text, whichever you prefer.
  6. Leave Destination as is, or choose another column. Click Finish.

You could also use two formulas in two separate columns.

To get the values to the left of the comma:

=0+LEFT(K1,FIND(",",K1)-1)

To get the values to the right of the comma:

=0+RIGHT(K1,LEN(K1)-FIND(",",K1))

where K1 contains the initial string, such as 401.50,0.027

** 0+ before the formulas converts the extracted substrings to numerical data.

share|improve this answer
    
+1 for Text to Columns. –  Excellll Oct 31 '11 at 16:56
add comment

Copy/Paste the text into a text editor Replace all space characters with carriagereturn/linefeeds Save as a TXT file. Open the file in Excel.

share|improve this answer
add comment

write a little vba to process your list

This assumes the list of values is in a single cell and is selected. Puts the results in the following cells

Sub zx()
    Dim a() As String
    Dim v As Variant
    Dim i As Long
    Dim j As Long

    a = Split(ActiveCell.Value, " ")
    ReDim v(1 To UBound(a) + 1, 1 To 2)
    For i = 1 To UBound(a) + 1
        j = InStr(a(i - 1), ",")
        v(i, 1) = Val(Left(a(i - 1), j - 1))
        v(i, 2) = Val(Mid(a(i - 1), j + 1))
    Next

    ActiveCell.Offset(1, 0).Resize(UBound(a) + 1, 2) = v
End Sub
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.