Turn 1 column into 2 columns

[Liliquestionsxlxs]

I have this column and I want to automatically make it become two columns where the / divides the two distinct data points

.103/.104

.103/.106

.099/.100

.101/.102

.099/.101

.098/.100

.096/.103

.100/.102

.098/.100

.100/.102

.098/.100

.100/.102

.093/.094

.094/.095

.092/.094

.092/.094

.091/.094

.094/.095

.092/.095

.094/.094

.090/.091

.093/.094

.095/.096

.092/.092

.093/.094

.091/.095

.091/.093

.091/.092

 

[lrobbo314]

Could use the text to columns feature, or use formulas, or use Power Query. Formulas and PQ below.

BookII.xlsm
	A	B	C	D	E	F
1	Values	Value A	Value B		Values.1	Values.2
2	.103/.104	.103	.104		.103	.104
3	.103/.106	.103	.106		.103	.106
4	.099/.100	.099	.100		.099	.100
5	.101/.102	.101	.102		.101	.102
6	.099/.101	.099	.101		.099	.101
7	.098/.100	.098	.100		.098	.100
8	.096/.103	.096	.103		.096	.103
9	.100/.102	.100	.102		.100	.102
10	.098/.100	.098	.100		.098	.100
11	.100/.102	.100	.102		.100	.102
12	.098/.100	.098	.100		.098	.100
13	.100/.102	.100	.102		.100	.102
14	.093/.094	.093	.094		.093	.094
15	.094/.095	.094	.095		.094	.095
16	.092/.094	.092	.094		.092	.094
17	.092/.094	.092	.094		.092	.094
18	.091/.094	.091	.094		.091	.094
19	.094/.095	.094	.095		.094	.095
20	.092/.095	.092	.095		.092	.095
21	.094/.094	.094	.094		.094	.094
22	.090/.091	.090	.091		.090	.091
23	.093/.094	.093	.094		.093	.094
24	.095/.096	.095	.096		.095	.096
25	.092/.092	.092	.092		.092	.092
26	.093/.094	.093	.094		.093	.094
27	.091/.095	.091	.095		.091	.095
28	.091/.093	.091	.093		.091	.093
29	.091/.092	.091	.092		.091	.092
Sheet10

Cell Formulas
Range	Formula
B2:B29	B2	=LEFT(A2,FIND("/",A2)-1)
C2:C29	C2	=RIGHT(A2,LEN(A2)-FIND("/",A2))

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Values", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Values.1", "Values.2"})
in
    Split

 

[Fluff]

If your data is always like that, you could also use

Fluff.xlsm
	A	B	C
1
2	.103/.104	0.103	0.104
3	.103/.106	0.103	0.106
4	.099/.100	0.099	0.1
5	.101/.102	0.101	0.102
6	.099/.101	0.099	0.101
7	.098/.100	0.098	0.1
8	.096/.103	0.096	0.103
9	.100/.102	0.1	0.102
10	.098/.100	0.098	0.1
11	.100/.102	0.1	0.102
12	.098/.100	0.098	0.1
13	.100/.102	0.1	0.102
14	.093/.094	0.093	0.094
15	.094/.095	0.094	0.095
16	.092/.094	0.092	0.094
17	.092/.094	0.092	0.094
18	.091/.094	0.091	0.094
19	.094/.095	0.094	0.095
20	.092/.095	0.092	0.095
21	.094/.094	0.094	0.094
22	.090/.091	0.09	0.091
23	.093/.094	0.093	0.094
24	.095/.096	0.095	0.096
25	.092/.092	0.092	0.092
26	.093/.094	0.093	0.094
27	.091/.095	0.091	0.095
28	.091/.093	0.091	0.093
29	.091/.092	0.091	0.092
Main

Cell Formulas
Range	Formula
B2:C29	B2	=MID(A2,{1,6},4)+0
Dynamic array formulas.

 

[lrobbo314]

@Fluff Haven't seen mid used that way yet, cool. Dynamic array formulas won't be available in XL 2021 though, right?

 

[Fluff]

Dynamic array formulas won't be available in XL 2021 though, right?

Nope, 2021 has almost everything that 365 has other than Lambda.

 

[lrobbo314]

Oh, cool. Good to know.