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.

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.

2

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.

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy