Text to Columns Formula

ftlab

New Member
Joined
Mar 14, 2019
Messages
4
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
have you tried the built-in text to columns option with / as delimiter?
 
Last edited:
Upvote 0
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!
 
Upvote 0
with PowerQuery it will be separated automatically

rawraw.1raw.2raw.3raw.4raw.5raw.6
20.8 / 20.1 / 17.7 / 15.9 / 13.1 / 6.0
20.8​
20.1​
17.7​
15.9​
13.1​
6​
21.0 / 19.2 / 18.3 / 17.3 / 15.8 / 12.0
21​
19.2​
18.3​
17.3​
15.8​
12​
19.9 / 12.3 / 9.1 / 8.2 / 7.1 / 5.0
19.9​
12.3​
9.1​
8.2​
7.1​
5​
21.1 / 20.3 / 18.1 / 16.4 / 14.2 / 6.0
21.1​
20.3​
18.1​
16.4​
14.2​
6​
21.2 / 15.3 / 12.2 / 9.4 / 7.3 / 3.1
21.2​
15.3​
12.2​
9.4​
7.3​
3.1​
21.2 / 20.3 / 18.1 / 16.3 / 14.1 / 12.2
21.2​
20.3​
18.1​
16.3​
14.1​
12.2​

Code:
[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 ;)
 
Upvote 0
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))
 
Upvote 0
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
ABCDEFG
120.8 / 20.1 / 17.7 / 15.9 / 13.1 / 6.020.820.117.715.913.16
221.0 / 19.2 / 18.3 / 17.3 / 15.8 / 12.02119.218.317.315.812
319.9 / 12.3 / 9.1 / 8.2 / 7.1 / 5.019.912.39.18.27.15
421.1 / 20.3 / 18.1 / 16.4 / 14.2 / 6.021.120.318.116.414.26
521.2 / 15.3 / 12.2 / 9.4 / 7.3 / 3.121.215.312.29.47.33.1
621.2 / 20.3 / 18.1 / 16.3 / 14.1 / 12.221.220.318.116.314.112.2
Sheet1
Cell Formulas
RangeFormula
B1=0+(MID(SUBSTITUTE($A1,"/",REPT(" ",99)),COLUMNS($B1:B1)*99-98,99))
 
Upvote 0
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!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top