Need to split X Y data into two columns

[greg544]

I have a .csv file that has graph data. There are no labels included. Here is a sample...

-1, 1, -1, 2, -1, 3, -1, 4, -.05, 5

In this example the 1st, 3rd, 5th, 7th & 9th numbers need to be in the X column (I made all of these negative numbers). The 2nd, 4th, 6th, 8th & 10th need to go into the Y Column. I have about 2500 values in the string.

So I need...

X Y
-1 1
-1 2
-1 3
-1 4
-.05 5

Thank you.

 

[kweaver]

How about:

B2:

=1*LEFT(A2,FIND(" ",A2))

C2:

=1*RIGHT(A2,LEN(A2)-FIND(" ",A2))[/code'

OR are these values ALL in A2 as a string?

 

[greg544]

=1*RIGHT(A2,LEN(A2)-FIND(" ",A2))

They are all in A...

-1​

1​

-1​

2​

-1​

3​

-1​

4​

-0.05​

5​

 

[teylyn]

Can you please clarify? First you say your data is in a csv file in this format

-1, 1, -1, 2, -1, 3, -1, 4, -.05, 5

Then you say the data is in cells in column A.

Which is it? Because the approach will be dramatically different based on how the data is organised.

If you can't describe it, take a screenshot and post that.

 

[greg544]

Sorry for confusing the issue. It is in a csv file.

When I imported into excel I put all the values into A so I could experiment, but I need to do this directly from the csv.

 

[kweaver]

Does it look like A1 or like B1:B10 ?

 

[greg544]

A1

 

[teylyn]

Load the CSV and let Excel split it out into individual columns in row 1. Excel has over 16,000 columns, so you should be good for quite a few numbers.

Then use this for X
=INDEX($1:$1,(ROW(A1)*2)-1)

and this for Y
=INDEX($1:$1,ROW(A1)*2)

Copy down as far as you need.

 

[kweaver]

This is pretty ugly, but think it works:

Book3
	A	B	C	D
1	1, 1, -1, 3, 2, -1, 5, -1, 6, 0.05, -.05, 8		1	1
2			-1	3
3			2	-1
4			5	-1
5			6	0.05
6			-0.05	8
Sheet1

Cell Formulas
Range	Formula
C1:C6	C1	=1*MID($A$1,FIND(CHAR(160),SUBSTITUTE(","&$A$1,",",CHAR(160),1+2*(ROW()-1))),FIND(CHAR(160),SUBSTITUTE($A$1&",",",",CHAR(160),1+2*(ROW()-1)))-FIND(CHAR(160),SUBSTITUTE(","&$A$1,",",CHAR(160),1+2*(ROW()-1))))
D1:D6	D1	=1*MID($A$1,FIND(CHAR(160),SUBSTITUTE(","&$A$1,",",CHAR(160),2*ROW())),FIND(CHAR(160),SUBSTITUTE($A$1&",",",",CHAR(160),2*ROW()))-FIND(CHAR(160),SUBSTITUTE(","&$A$1,",",CHAR(160),2*ROW())))

 

[sandy666]

just for fun with Power Query

raw		X	Y
-1, 1, -1, 2, -1, 3, -1, 4, -.05, 5		-1	1
		-1	2
		-1	3
		-1	4
		-.05	5

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split2Rows = Table.ExpandListColumn(Table.TransformColumns(Source, {{"raw", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "raw"),
    Integer = Table.TransformColumns(Table.AddIndexColumn(Split2Rows, "Index", 0, 1), {{"Index", each Number.IntegerDivide(_, 2), Int64.Type}}),
    Extract = Table.TransformColumns(Table.AddColumn(Table.Group(Integer, {"Index"}, {{"Count", each _, type table}}), "Result", each Table.Column([Count],"raw")), {"Result", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Split2Cols = Table.SplitColumn(Extract, "Result", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Result.1", "Result.2"}),
    Result = Table.SelectColumns(Table.RenameColumns(Split2Cols,{{"Result.1", "X"}, {"Result.2", "Y"}}),{"X", "Y"})
in
    Result