Cut up a String into different cells

[Jaredhott]

Hey I am a football coach we use excel to script our practices. I want our long script string to be broken up into separate columns.

Here is an example:
In excel I have: 1 split H Across 67 Hitch Rt

I want to split up the Formation (1 Split) the motion (H Across) the hole (67) and the play (hitch rt)

I realize this is pretty easy for one play but here is the catch. Our formation could be 1, 1 split, 1 wide, 1 tight, or 2, 7, 8 with either no word or the same word after it. Some plays have motions and some don't. If someone is willing to help me with this I could send you a file with our script in it.

 

[mole999]

the question will be are they same length segments, common themes that can be pulled, i.e always has commas at a location

 

[Jaredhott]

the question will be are they same length segments, common themes that can be pulled, i.e always has commas at a location

Here is an example. On the left is what I have. On the right is how I want it split up. Will this be possible?

All in one Column		Column 1	Column 2	Column 3	Column 4	Column 5
2 Rip 71 Snag Rt		2	Rip	71	Snag rt
2 Split Eagle T Fly 26 Dunk		2 Split	Eagle	26	Dunk	T Fly
7 Split Zone Auto		7 Split			Zone
1 Split 25 Dunk Pass		1 Split		25	Dunk Pass
1 Wide Rip 60 H Shallow		1 Wide	Rip	60	H Shallow
1 Rip 70 Y Shallow		1	Rip	70	Y Shallow
2 Wide Liz Dunk Move Auto		2 Wide	Liz		Dunk
7 Split 25 Dunk Pass		7 Split		25	Dunk Pass
2 Split H Across 77 Hitch Rt		2 Split		77	Hitch Rt	H Across
1 Split Robin T Fly 17 Jet		1 Split	Robin	17	Jet	T Fly
2 Liz 18 Jet Pass		2	Liz	18	Jet Pass
2 Wide Robin T Fly 15 Power		2 Wide	Robin	15	Power	T Fly
2 Liz 18 Jet		2	Liz	18	Jet
1 Split H Fly 17 Jet Pass		1 Split		17	Jet Pass	H Fly
1 Wide Robin T Fly 23 Zone		1 Wide	Robin	23	Zone	T Fly

<colgroup><col><col span="6"></colgroup><tbody>
</tbody>

 

[shg]

Just rearranging the rows, it's apparent that there are some rules in play that need to be exposed -- including the fact that the output is sometimes not in the order of the input.

	A​	B​	C​	D​	E​	F​	G​
1​	All in one Column​	​	Column 1​	Column 2​	Column 3​	Column 4​	Column 5​
2​	2 Rip 71 Snag Rt		2​	Rip​	71​	Snag rt​	​
3​	2 Liz 18 Jet		2​	Liz​	18​	Jet​	​
4​	1 Rip 70 Y Shallow		1​	Rip​	70​	Y Shallow​	​
5​	2 Liz 18 Jet Pass		2​	Liz​	18​	Jet Pass​	​
6​	2 Split Eagle T Fly 26 Dunk		2 Split​	Eagle​	26​	Dunk​	T Fly​
7​	7 Split Zone Auto		7 Split​	​	​	Zone​	​
8​	1 Split 25 Dunk Pass		1 Split​	​	25​	Dunk Pass​	​
9​	1 Wide Rip 60 H Shallow		1 Wide​	Rip​	60​	H Shallow​	​
10​	2 Wide Liz Dunk Move Auto		2 Wide​	Liz​	​	Dunk​	​
11​	7 Split 25 Dunk Pass		7 Split​	​	25​	Dunk Pass​	​
12​	2 Split H Across 77 Hitch Rt		2 Split​	​	77​	Hitch Rt​	H Across​
13​	1 Split Robin T Fly 17 Jet		1 Split​	Robin​	17​	Jet​	T Fly​
14​	2 Wide Robin T Fly 15 Power		2 Wide​	Robin​	15​	Power​	T Fly​
15​	1 Split H Fly 17 Jet Pass		1 Split​	​	17​	Jet Pass​	H Fly​
16​	1 Wide Robin T Fly 23 Zone		1 Wide​	Robin​	23​	Zone​	T Fly​
17​

 

[paulsolar]

Hi

I'm following this thread as I have something similar to do later today but quite a bit simpler.

I have a cell with numbers in it which are separated by either comma's or a dash like:

Example 1

34,12,2,9,25

Example 2

33-23-19-10 13

What I'd like to do is split them up into individual cells to use in an array somewhere else. The outcome would be that the all the numbers are in say A1 and the split out number would be in B1 C1 ....... and so on until all the numbers between the comma's or dashes are used up. (be nice if I could get them in numerical order as well)

I'm not quite sure if I should of added this to this thread but as the topics are so similar I thought it would be good idea.

Please excuse me if I shouldn't have

Kind regards

Paul

 

[shg]

Why not start your own thread, paul?

 

[paulsolar]

Hi Shg

will do

cheers

Paul

 

[Jaredhott]

Just rearranging the rows, it's apparent that there are some rules in play that need to be exposed -- including the fact that the output is sometimes not in the order of the input.

	A​	B​	C​	D​	E​	F​	G​
1​	All in one Column​		Column 1​	Column 2​	Column 3​	Column 4​	Column 5​
2​	2 Rip 71 Snag Rt		2​	Rip​	71​	Snag rt​
3​	2 Liz 18 Jet		2​	Liz​	18​	Jet​
4​	1 Rip 70 Y Shallow		1​	Rip​	70​	Y Shallow​
5​	2 Liz 18 Jet Pass		2​	Liz​	18​	Jet Pass​
6​	2 Split Eagle T Fly 26 Dunk		2 Split​	Eagle​	26​	Dunk​	T Fly​
7​	7 Split Zone Auto		7 Split​			Zone​
8​	1 Split 25 Dunk Pass		1 Split​		25​	Dunk Pass​
9​	1 Wide Rip 60 H Shallow		1 Wide​	Rip​	60​	H Shallow​
10​	2 Wide Liz Dunk Move Auto		2 Wide​	Liz​		Dunk​
11​	7 Split 25 Dunk Pass		7 Split​		25​	Dunk Pass​
12​	2 Split H Across 77 Hitch Rt		2 Split​		77​	Hitch Rt​	H Across​
13​	1 Split Robin T Fly 17 Jet		1 Split​	Robin​	17​	Jet​	T Fly​
14​	2 Wide Robin T Fly 15 Power		2 Wide​	Robin​	15​	Power​	T Fly​
15​	1 Split H Fly 17 Jet Pass		1 Split​		17​	Jet Pass​	H Fly​
16​	1 Wide Robin T Fly 23 Zone		1 Wide​	Robin​	23​	Zone​	T Fly​
17​

<tbody>
</tbody>

So if I put column 5 as column 3 and push the other two out that would help?

 

[shg]

It would certainly help if the order were unchanged.

 

[Jaredhott]

It would certainly help if the order were unchanged.

Okay I can change the order no problem. What should I do to get it split up properly?