Excel formula to separate data at each comma

galmond1010

New Member
Joined
Apr 15, 2019
Messages
21
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
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,993
Office Version
365
Platform
Windows
You could use text to columns on the data tab. Using the comma as the separator.
 

galmond1010

New Member
Joined
Apr 15, 2019
Messages
21
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.
 

shaowu459

Active Member
Joined
Apr 26, 2018
Messages
348
Office Version
365
Platform
Windows
Book1.xlsx
ABC
1Food:.1,Retail:.8, Pkg:.1
2
3
Food:.1
Retail:.8
Pkg:.1
Sheet4
Cell Formulas
RangeFormula
A3:C3A3=TRIM(MID(SUBSTITUTE($A$1,",",REPT(" ",999)),(COLUMN(A1)-1)*999+1,999))
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,438
Office Version
2019
Platform
Windows
An alternative solution is to use Power Query. Mcode follows

Rich (BB code):
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"
 

Watch MrExcel Video

Forum statistics

Threads
1,096,183
Messages
5,448,846
Members
405,533
Latest member
Heretical1

This Week's Hot Topics

Top