Text to Columns Formula

[ftlab]

I'm hoping someone can help me with splitting a column of data into six separate columns.
I've tried using various functions with no luck, as the numbers can be different lengths, although each are separated by a forward slash. The "Text to Columns" feature isn't really an option as I'm hoping a formula can do this without human intervention. If possible, I would prefer to avoid using a script as coding is a little beyond my understanding, although willing to learn if it's the only option.
A typical column of data would look like:

20.8 / 20.1 / 17.7 / 15.9 / 13.1 / 6.0
21.0 / 19.2 / 18.3 / 17.3 / 15.8 / 12.0
19.9 / 12.3 / 9.1 / 8.2 / 7.1 / 5.0
21.1 / 20.3 / 18.1 / 16.4 / 14.2 / 6.0
21.2 / 15.3 / 12.2 / 9.4 / 7.3 / 3.1
21.2 / 20.3 / 18.1 / 16.3 / 14.1 / 12.2

Any help would be most appreciated.

 

[AlanY]

have you tried the built-in text to columns option with / as delimiter?

 

[ftlab]

Yes, thats does work using "/" as a delimiter, but I'm trying to automate this with a formula so imported data is automatically seperated out into columns. Proving to be quite tricky!

 

[sandy666]

with PowerQuery it will be separated automatically

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]raw.1[/td][td=bgcolor:#70AD47]raw.2[/td][td=bgcolor:#70AD47]raw.3[/td][td=bgcolor:#70AD47]raw.4[/td][td=bgcolor:#70AD47]raw.5[/td][td=bgcolor:#70AD47]raw.6[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]20.8 / 20.1 / 17.7 / 15.9 / 13.1 / 6.0[/td][td][/td][td=bgcolor:#E2EFDA]

20.8​

[/td][td=bgcolor:#E2EFDA]

20.1​

[/td][td=bgcolor:#E2EFDA]

17.7​

[/td][td=bgcolor:#E2EFDA]

15.9​

[/td][td=bgcolor:#E2EFDA]

13.1​

[/td][td=bgcolor:#E2EFDA]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]21.0 / 19.2 / 18.3 / 17.3 / 15.8 / 12.0[/td][td][/td][td]

21​

[/td][td]

19.2​

[/td][td]

18.3​

[/td][td]

17.3​

[/td][td]

15.8​

[/td][td]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]19.9 / 12.3 / 9.1 / 8.2 / 7.1 / 5.0[/td][td][/td][td=bgcolor:#E2EFDA]

19.9​

[/td][td=bgcolor:#E2EFDA]

12.3​

[/td][td=bgcolor:#E2EFDA]

9.1​

[/td][td=bgcolor:#E2EFDA]

8.2​

[/td][td=bgcolor:#E2EFDA]

7.1​

[/td][td=bgcolor:#E2EFDA]

5​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]21.1 / 20.3 / 18.1 / 16.4 / 14.2 / 6.0[/td][td][/td][td]

21.1​

[/td][td]

20.3​

[/td][td]

18.1​

[/td][td]

16.4​

[/td][td]

14.2​

[/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]21.2 / 15.3 / 12.2 / 9.4 / 7.3 / 3.1[/td][td][/td][td=bgcolor:#E2EFDA]

21.2​

[/td][td=bgcolor:#E2EFDA]

15.3​

[/td][td=bgcolor:#E2EFDA]

12.2​

[/td][td=bgcolor:#E2EFDA]

9.4​

[/td][td=bgcolor:#E2EFDA]

7.3​

[/td][td=bgcolor:#E2EFDA]

3.1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]21.2 / 20.3 / 18.1 / 16.3 / 14.1 / 12.2[/td][td][/td][td]

21.2​

[/td][td]

20.3​

[/td][td]

18.1​

[/td][td]

16.3​

[/td][td]

14.1​

[/td][td]

12.2​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table17"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "raw", Splitter.SplitTextByDelimiter(" / ", QuoteStyle.Csv), {"raw.1", "raw.2", "raw.3", "raw.4", "raw.5", "raw.6"})
in
    #"Split Column by Delimiter"[/SIZE]

and no, this is not a vba

 

[Fluff]

With formulae
In B2 copied down
=TRIM(LEFT(A2,FIND("/",A2)-1))
In C2 copied down & across
=TRIM(MID($A2,FIND("|",SUBSTITUTE($A2,"/","|",COLUMN(A$1)))+1,FIND("/",$A2)-1))

 

[FormR]

I'm trying to automate this with a formula

Hi, welcome to the forum! See this link for a formula option.

http://www.excelfox.com/forum/showthread.php/333-Get-Field-from-Delimited-Text-String

For example:

Excel 2013/2016
	A	B	C	D	E	F	G
1	20.8 / 20.1 / 17.7 / 15.9 / 13.1 / 6.0	20.8	20.1	17.7	15.9	13.1	6
2	21.0 / 19.2 / 18.3 / 17.3 / 15.8 / 12.0	21	19.2	18.3	17.3	15.8	12
3	19.9 / 12.3 / 9.1 / 8.2 / 7.1 / 5.0	19.9	12.3	9.1	8.2	7.1	5
4	21.1 / 20.3 / 18.1 / 16.4 / 14.2 / 6.0	21.1	20.3	18.1	16.4	14.2	6
5	21.2 / 15.3 / 12.2 / 9.4 / 7.3 / 3.1	21.2	15.3	12.2	9.4	7.3	3.1
6	21.2 / 20.3 / 18.1 / 16.3 / 14.1 / 12.2	21.2	20.3	18.1	16.3	14.1	12.2
Sheet1

Cell Formulas
Range	Formula
B1	=0+(MID(SUBSTITUTE($A1,"/",REPT(" ",99)),COLUMNS($B1:B1)*99-98,99))

 

[ftlab]

Sandy666, thanks very much for the quick response and PowerQuery option, this is new to me so will take a while to understand, but will be very useful to learn how to use this.

Fluff & FormR thanks for the formulas, both work perfectly.

All of you have been a great help, so many thanks!

 

[Fluff]

Glad we could help & thanks for the feedback

 

[sandy666]

PQ is worth to learn

have a nice day