Cut up a String into different cells

J

Jaredhott

New Member
Joined
May 3, 2013
Messages
19
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.
 
Sort by date Sort by votes
the question will be are they same length segments, common themes that can be pulled, i.e always has commas at a location
 
Upvote 0
mole999 said:
the question will be are they same length segments, common themes that can be pulled, i.e always has commas at a location
Click to expand...

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?

[TABLE="width: 568"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD]All in one Column[/TD]
[TD][/TD]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[TD]Column 5[/TD]
[/TR]
[TR]
[TD]2 Rip 71 Snag Rt[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]Rip[/TD]
[TD="align: right"]71[/TD]
[TD]Snag rt[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Split Eagle T Fly 26 Dunk[/TD]
[TD][/TD]
[TD]2 Split[/TD]
[TD]Eagle[/TD]
[TD="align: right"]26[/TD]
[TD]Dunk[/TD]
[TD]T Fly[/TD]
[/TR]
[TR]
[TD]7 Split Zone Auto[/TD]
[TD][/TD]
[TD]7 Split[/TD]
[TD][/TD]
[TD][/TD]
[TD]Zone[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Split 25 Dunk Pass[/TD]
[TD][/TD]
[TD]1 Split[/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD="colspan: 2"]Dunk Pass[/TD]
[/TR]
[TR]
[TD]1 Wide Rip 60 H Shallow[/TD]
[TD][/TD]
[TD]1 Wide[/TD]
[TD]Rip[/TD]
[TD="align: right"]60[/TD]
[TD="colspan: 2"]H Shallow[/TD]
[/TR]
[TR]
[TD]1 Rip 70 Y Shallow[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]Rip[/TD]
[TD="align: right"]70[/TD]
[TD]Y Shallow[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Wide Liz Dunk Move Auto[/TD]
[TD][/TD]
[TD]2 Wide[/TD]
[TD]Liz[/TD]
[TD][/TD]
[TD]Dunk[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7 Split 25 Dunk Pass[/TD]
[TD][/TD]
[TD]7 Split[/TD]
[TD][/TD]
[TD="align: right"]25[/TD]
[TD="colspan: 2"]Dunk Pass[/TD]
[/TR]
[TR]
[TD]2 Split H Across 77 Hitch Rt[/TD]
[TD][/TD]
[TD]2 Split[/TD]
[TD][/TD]
[TD="align: right"]77[/TD]
[TD]Hitch Rt[/TD]
[TD]H Across[/TD]
[/TR]
[TR]
[TD]1 Split Robin T Fly 17 Jet[/TD]
[TD][/TD]
[TD]1 Split[/TD]
[TD]Robin[/TD]
[TD="align: right"]17[/TD]
[TD]Jet[/TD]
[TD]T Fly[/TD]
[/TR]
[TR]
[TD]2 Liz 18 Jet Pass[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]Liz[/TD]
[TD="align: right"]18[/TD]
[TD]Jet Pass[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2 Wide Robin T Fly 15 Power[/TD]
[TD][/TD]
[TD]2 Wide[/TD]
[TD]Robin[/TD]
[TD="align: right"]15[/TD]
[TD]Power[/TD]
[TD]T Fly[/TD]
[/TR]
[TR]
[TD]2 Liz 18 Jet[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]Liz[/TD]
[TD="align: right"]18[/TD]
[TD]Jet[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1 Split H Fly 17 Jet Pass[/TD]
[TD][/TD]
[TD]1 Split[/TD]
[TD][/TD]
[TD="align: right"]17[/TD]
[TD]Jet Pass[/TD]
[TD]H Fly[/TD]
[/TR]
[TR]
[TD]1 Wide Robin T Fly 23 Zone[/TD]
[TD][/TD]
[TD]1 Wide[/TD]
[TD]Robin[/TD]
[TD="align: right"]23[/TD]
[TD]Zone[/TD]
[TD]T Fly[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.

[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr]
[tr][td]
1​
[/td][td]
All in one Column​
[/td][td]
​
[/td][td]
Column 1​
[/td][td]
Column 2​
[/td][td]
Column 3​
[/td][td]
Column 4​
[/td][td]
Column 5​
[/td][/tr]

[tr][td]
2​
[/td][td]2 Rip 71 Snag Rt[/td][td][/td][td]
2​
[/td][td]
Rip​
[/td][td]
71​
[/td][td]
Snag rt​
[/td][td]
​
[/td][/tr]

[tr][td]
3​
[/td][td]2 Liz 18 Jet[/td][td][/td][td]
2​
[/td][td]
Liz​
[/td][td]
18​
[/td][td]
Jet​
[/td][td]
​
[/td][/tr]

[tr][td]
4​
[/td][td]1 Rip 70 Y Shallow[/td][td][/td][td]
1​
[/td][td]
Rip​
[/td][td]
70​
[/td][td]
Y Shallow​
[/td][td]
​
[/td][/tr]

[tr][td]
5​
[/td][td]2 Liz 18 Jet Pass[/td][td][/td][td]
2​
[/td][td]
Liz​
[/td][td]
18​
[/td][td]
Jet Pass​
[/td][td]
​
[/td][/tr]

[tr][td]
6​
[/td][td]2 Split Eagle T Fly 26 Dunk[/td][td][/td][td]
2 Split​
[/td][td]
Eagle​
[/td][td]
26​
[/td][td]
Dunk​
[/td][td]
T Fly​
[/td][/tr]

[tr][td]
7​
[/td][td]7 Split Zone Auto[/td][td][/td][td]
7 Split​
[/td][td]
​
[/td][td]
​
[/td][td]
Zone​
[/td][td]
​
[/td][/tr]

[tr][td]
8​
[/td][td]1 Split 25 Dunk Pass[/td][td][/td][td]
1 Split​
[/td][td]
​
[/td][td]
25​
[/td][td]
Dunk Pass​
[/td][td]
​
[/td][/tr]

[tr][td]
9​
[/td][td]1 Wide Rip 60 H Shallow[/td][td][/td][td]
1 Wide​
[/td][td]
Rip​
[/td][td]
60​
[/td][td]
H Shallow​
[/td][td]
​
[/td][/tr]

[tr][td]
10​
[/td][td]2 Wide Liz Dunk Move Auto[/td][td][/td][td]
2 Wide​
[/td][td]
Liz​
[/td][td]
​
[/td][td]
Dunk​
[/td][td]
​
[/td][/tr]

[tr][td]
11​
[/td][td]7 Split 25 Dunk Pass[/td][td][/td][td]
7 Split​
[/td][td]
​
[/td][td]
25​
[/td][td]
Dunk Pass​
[/td][td]
​
[/td][/tr]

[tr][td]
12​
[/td][td]2 Split H Across 77 Hitch Rt[/td][td][/td][td]
2 Split​
[/td][td]
​
[/td][td]
77​
[/td][td]
Hitch Rt​
[/td][td]
H Across​
[/td][/tr]

[tr][td]
13​
[/td][td]1 Split Robin T Fly 17 Jet[/td][td][/td][td]
1 Split​
[/td][td]
Robin​
[/td][td]
17​
[/td][td]
Jet​
[/td][td]
T Fly​
[/td][/tr]

[tr][td]
14​
[/td][td]2 Wide Robin T Fly 15 Power[/td][td][/td][td]
2 Wide​
[/td][td]
Robin​
[/td][td]
15​
[/td][td]
Power​
[/td][td]
T Fly​
[/td][/tr]

[tr][td]
15​
[/td][td]1 Split H Fly 17 Jet Pass[/td][td][/td][td]
1 Split​
[/td][td]
​
[/td][td]
17​
[/td][td]
Jet Pass​
[/td][td]
H Fly​
[/td][/tr]

[tr][td]
16​
[/td][td]1 Wide Robin T Fly 23 Zone[/td][td][/td][td]
1 Wide​
[/td][td]
Robin​
[/td][td]
23​
[/td][td]
Zone​
[/td][td]
T Fly​
[/td][/tr]

[tr][td]
17​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
 
Upvote 0
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
 
Last edited:
Upvote 0
Why not start your own thread, paul?
 
Upvote 0
shg said:
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.

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]
All in one Column​
[/TD]
[TD][/TD]
[TD]
Column 1​
[/TD]
[TD]
Column 2​
[/TD]
[TD]
Column 3​
[/TD]
[TD]
Column 4​
[/TD]
[TD]
Column 5​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]2 Rip 71 Snag Rt[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
Rip​
[/TD]
[TD]
71​
[/TD]
[TD]
Snag rt​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]2 Liz 18 Jet[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
Liz​
[/TD]
[TD]
18​
[/TD]
[TD]
Jet​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]1 Rip 70 Y Shallow[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
Rip​
[/TD]
[TD]
70​
[/TD]
[TD]
Y Shallow​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]2 Liz 18 Jet Pass[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
Liz​
[/TD]
[TD]
18​
[/TD]
[TD]
Jet Pass​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]2 Split Eagle T Fly 26 Dunk[/TD]
[TD][/TD]
[TD]
2 Split​
[/TD]
[TD]
Eagle​
[/TD]
[TD]
26​
[/TD]
[TD]
Dunk​
[/TD]
[TD]
T Fly​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]7 Split Zone Auto[/TD]
[TD][/TD]
[TD]
7 Split​
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
Zone​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]1 Split 25 Dunk Pass[/TD]
[TD][/TD]
[TD]
1 Split​
[/TD]
[TD][/TD]
[TD]
25​
[/TD]
[TD]
Dunk Pass​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]1 Wide Rip 60 H Shallow[/TD]
[TD][/TD]
[TD]
1 Wide​
[/TD]
[TD]
Rip​
[/TD]
[TD]
60​
[/TD]
[TD]
H Shallow​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]2 Wide Liz Dunk Move Auto[/TD]
[TD][/TD]
[TD]
2 Wide​
[/TD]
[TD]
Liz​
[/TD]
[TD][/TD]
[TD]
Dunk​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
11​
[/TD]
[TD]7 Split 25 Dunk Pass[/TD]
[TD][/TD]
[TD]
7 Split​
[/TD]
[TD][/TD]
[TD]
25​
[/TD]
[TD]
Dunk Pass​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
12​
[/TD]
[TD]2 Split H Across 77 Hitch Rt[/TD]
[TD][/TD]
[TD]
2 Split​
[/TD]
[TD][/TD]
[TD]
77​
[/TD]
[TD]
Hitch Rt​
[/TD]
[TD]
H Across​
[/TD]
[/TR]
[TR]
[TD]
13​
[/TD]
[TD]1 Split Robin T Fly 17 Jet[/TD]
[TD][/TD]
[TD]
1 Split​
[/TD]
[TD]
Robin​
[/TD]
[TD]
17​
[/TD]
[TD]
Jet​
[/TD]
[TD]
T Fly​
[/TD]
[/TR]
[TR]
[TD]
14​
[/TD]
[TD]2 Wide Robin T Fly 15 Power[/TD]
[TD][/TD]
[TD]
2 Wide​
[/TD]
[TD]
Robin​
[/TD]
[TD]
15​
[/TD]
[TD]
Power​
[/TD]
[TD]
T Fly​
[/TD]
[/TR]
[TR]
[TD]
15​
[/TD]
[TD]1 Split H Fly 17 Jet Pass[/TD]
[TD][/TD]
[TD]
1 Split​
[/TD]
[TD][/TD]
[TD]
17​
[/TD]
[TD]
Jet Pass​
[/TD]
[TD]
H Fly​
[/TD]
[/TR]
[TR]
[TD]
16​
[/TD]
[TD]1 Wide Robin T Fly 23 Zone[/TD]
[TD][/TD]
[TD]
1 Wide​
[/TD]
[TD]
Robin​
[/TD]
[TD]
23​
[/TD]
[TD]
Zone​
[/TD]
[TD]
T Fly​
[/TD]
[/TR]
[TR]
[TD]
17​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

So if I put column 5 as column 3 and push the other two out that would help?
 
Upvote 0
It would certainly help if the order were unchanged.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Big, Multi-Step Macro for Efficiently Cutting Big Pieces Into Small Pieces
Replies
1
Views
595
Solon Aquila
S
L
  • Solved
Worksheet Change_Event not working on cell value change
Replies
9
Views
564
Lil Stinker
L
N
  • Solved
VBA Excel opens Word - Split Screen to Windows Planes
Replies
5
Views
1K
NdNoviceHlp
NdNoviceHlp
C
  • Question Question
Create code that sends emails and creates a log
Replies
1
Views
416
cokeoverpepsi123
C
R
  • Question Question
Excel "Scripting.FileSystemObject" not releasing folder - *Permission Denied* when trying to rename it
2
Replies
12
Views
2K
RawlinsCross
R

Share this page

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