Excel formula to separate data at each comma

[galmond1010]

My column data extract from Oracle looks like this : Food:.1,Retail:.8, Pkg:.1 . For the example, these are vertical markets and their percentage. Is there a formula I could use to separate the data at each comma? I am assuming in this example I would use 3 columns to display the separated data. Any help would be greatly appreciated. Thank you

 

[Fluff]

You could use text to columns on the data tab. Using the comma as the separator.

 

[galmond1010]

I have tried that but it only sorts the data into 2 columns. Unless I am not using the text to columns correctly. The example above would show in column A Food:.1 and in column B Retail:.8, Pkg:.1. That's why I was wondering if it would be easier to use a formula instead. I will keep trying. Thank you.

 

[galmond1010]

Sorry the comma goes away after the text to columns.

 

[shaowu459]

Book1.xlsx
	A	B	C
1	Food:.1,Retail:.8, Pkg:.1
2
3	Food:.1	Retail:.8	Pkg:.1
Sheet4

Cell Formulas
Range	Formula
A3:C3	A3	=TRIM(MID(SUBSTITUTE($A$1,",",REPT(" ",999)),(COLUMN(A1)-1)*999+1,999))

 

[galmond1010]

Works beautifully. Thank you very much. Have a great weekend

 

[shaowu459]

Works beautifully. Thank you very much. Have a great weekend

Glad i could help you, have a nice weekend too~

 

[alansidman]

An alternative solution is to use Power Query. Mcode follows

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"})
in
    #"Split Column by Delimiter"