Extract variable text from strings

ziggyfo

New Member
Joined
Mar 24, 2021
Messages
24
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
Hi All,

I have been trying to find a way to extract some info with the left, right and mid fuctions without real success.

Below Is the info i receive, but want to have it split into certain parts

If you look at the first line, the info split is:

Time / Place / Runner / Date / Time

12:51 Swindon 2. Smash Hit-Win | Betfair Bet ID 1:228079100033 | Placed 24-Mar-21 12:49:43
12:46 Central Park 1. Leg It Liam-Win | Betfair Bet ID 1:228078762350 | Placed 24-Mar-21 12:44:41
12:43 Perry Barr 6. Right Badge-Win | Betfair Bet ID 1:228078524360 | Placed 24-Mar-21 12:41:44
12:39 Crayford 2. Mines A Pinkgin-Win | Betfair Bet ID 1:228078163707 | Placed 24-Mar-21 12:37:30

Would appreciate if anyone could guide me..

Thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Welcome to the Board!

If your example is what your data currently looks like, please post exactly what you want it to look like AFTER it is split.
 
Upvote 0
Welcome to the Board!

Depending on if some of the special characters can be relied on, then something like this might work:

Book1
ABCDEF
112:51 Swindon 2. Smash Hit-Win | Betfair Bet ID 1:228079100033 | Placed 24-Mar-21 12:49:4312:51SwindonSmash Hit24-Mar-2112:49:43
212:46 Central Park 1. Leg It Liam-Win | Betfair Bet ID 1:228078762350 | Placed 24-Mar-21 12:44:4112:46Central ParkLeg It Liam24-Mar-2112:44:41
312:43 Perry Barr 6. Right Badge-Win | Betfair Bet ID 1:228078524360 | Placed 24-Mar-21 12:41:4412:43Perry BarrRight Badge24-Mar-2112:41:44
412:39 Crayford 2. Mines A Pinkgin-Win | Betfair Bet ID 1:228078163707 | Placed 24-Mar-21 12:37:3012:39CrayfordMines A Pinkgin24-Mar-2112:37:30
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=TRIM(LEFT(A1,6))
C1:C4C1=TRIM(REPLACE(LEFT(A1,FIND(".",A1)-3),1,5,""))
D1:D4D1=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,".",REPT(" ",100)),"-",REPT(" ",100)),100,100))
E1:E4E1=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100))
F1:F4F1=TRIM(RIGHT(A1,8))
 
Upvote 0
Solution
Welcome to the Board!

If your example is what your data currently looks like, please post exactly what you want it to look like AFTER it is split.

Thanks Joe,

The information should be:


Time / Place / Runner / Date / Time
12:51 Swindon 2. Smash Hit-Win | Betfair Bet ID 1:228079100033 | Placed 24-Mar-21 12:49:43

Result:

12:51 | Swindon | Smash Hit | 24-Ma-21 12:49:43

The issue is that the "Runner" which in this case is "Smash Hit" is not always the same amount of characters as you can imagine it would be different names every time. The other bit of information that is not static is the place "Swindon" as this can change from race track to race track. Hope this made sense, btw this is a statement from betting as im sure you have gathered. so its the information that changes will be Place and Runner that will always be different.
 
Upvote 0
Welcome to the Board!

Depending on if some of the special characters can be relied on, then something like this might work:

Book1
ABCDEF
112:51 Swindon 2. Smash Hit-Win | Betfair Bet ID 1:228079100033 | Placed 24-Mar-21 12:49:4312:51SwindonSmash Hit24-Mar-2112:49:43
212:46 Central Park 1. Leg It Liam-Win | Betfair Bet ID 1:228078762350 | Placed 24-Mar-21 12:44:4112:46Central ParkLeg It Liam24-Mar-2112:44:41
312:43 Perry Barr 6. Right Badge-Win | Betfair Bet ID 1:228078524360 | Placed 24-Mar-21 12:41:4412:43Perry BarrRight Badge24-Mar-2112:41:44
412:39 Crayford 2. Mines A Pinkgin-Win | Betfair Bet ID 1:228078163707 | Placed 24-Mar-21 12:37:3012:39CrayfordMines A Pinkgin24-Mar-2112:37:30
Sheet1
Cell Formulas
RangeFormula
B1:B4B1=TRIM(LEFT(A1,6))
C1:C4C1=TRIM(REPLACE(LEFT(A1,FIND(".",A1)-3),1,5,""))
D1:D4D1=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,".",REPT(" ",100)),"-",REPT(" ",100)),100,100))
E1:E4E1=TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),200),100))
F1:F4F1=TRIM(RIGHT(A1,8))


Thank you Eric, your solution is perfect! These characters can be relied on as they will always be present in the string.

Appreciate the speed help.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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