split contents of cell into other cells

ronie85

Board Regular
Joined
Jan 25, 2014
Messages
96
Office Version
  1. 365
Platform
  1. Windows
in cell B1 I have [TABLE="width: 197"]
<tbody>[TR]
[TD]'Stoke 0-1 Aston Villa' how do I get this contents into 5 different cells. F1 'Stoke', G1 '0', H1 '-', I1 '1', J1 'Aston Villa'[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Try Date Text to Columns, delimited with Space as the delimiter. This will split Aston Villa into two (probably not a bad idea).
 
Upvote 0
I am pasting this from a website on a weekly basis and want the formula to be in place to strip the cell down into different cells. I want to make it as straight forward as possible.
 
Upvote 0
To get Stoke in a separate cell I can use =LEFT(B4,FIND("-",B4)-2) but I don't know how to get any of the other contents in the other cells
 
Upvote 0
Is this close to what you want? i typed in the dash(-) manually


Excel 2010
ABCDEF
1Stoke 0-1 Aston VillaStoke0-1Aston Villa
2Stoke City 2-4 Stoke cityStoke City2-4Stoke city
3man utd 10-2 Arsenalman utd10-2Arsenal
Sheet1
Cell Formulas
RangeFormula
B1=TRIM(LEFT(A1,FIND("-",A1)-3))
C1=MID(A1,FIND("-",A1)-2,2)
E1=MID(A1,FIND("-",A1)+1,FIND(" ",A1,FIND("-",A1))-FIND("-",A1)-1)
F1=MID(A1,FIND(" ",A1,FIND("-",A1))+1,99)
 
Upvote 0
That worked!! Thanks a lot! Plus I'm pleased that United beat Arsenal 10-2 lol
 
Upvote 0
Well, the "10" was only to show that the formula works with double figures :)

Glad to help
 
Upvote 0

Forum statistics

Threads
1,224,209
Messages
6,177,151
Members
452,762
Latest member
manuha

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