Strings...extracting and altering data...

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,831
Office Version
  1. 2010
Platform
  1. Windows
Can anyone help me with the following please... My expected results are highlighted... I would look at some youtube videos but my connection is on snail pace and I can't watch videos at the moment...

Excel Workbook
HIJKL
1417.03.200617-Mar-06Date
15( 0-0 )00Score to 2 Columns
161-010Score to 2 Columns
17( 0-0 )0 - 0Score to Single Column
181-01 - 0Score to Single Column
Sheet3
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Can you explain in words what your'e trying to achieve?
In particular why the scores for rows 15 and 16 are split into 2 columns but in rows 17 and 18 they stay in 1 column
 
Upvote 0
Hello…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
The values in column H have been downloaded so I want to get them in a format that I can work with…
<o:p> </o:p>
H14 is the date with the dots… I’m after the format in I14… or at least xl to recognise that it is a date and then I can change it to the format I want.
<o:p> </o:p>
H15 is the score in brackets… My current workbook has the scores in a two column format so I’d want the number(s) left of the “-“ in I16 and the number(s) right of the “-“ in J16…
<o:p> </o:p>
H16 is nearly the same as above only the data I downloaded isn’t in brackets…
<o:p> </o:p>
The last two are not that important if I get the above cracked… My next workbook is going to have the scores in one column as opposed to the two columns I currently use but I can take I15 (0) and J15 (0) and use =I15&” – “&J15 to get 0 – 0…
 
Upvote 0
H15 =SUBSTITUTE(SUBSTITUTE(H15,"(",""),")","") has removed the brackets...
 
Upvote 0
It's a mess but it is close enough for government work as I delete all the mess once I get the numbers out of the brackets...

Excel Workbook
GHIJK
23( 1-0 )1-01 - 010
Sheet3


It's the date one that I'm struggling with... any ideas anyone please?
 
Upvote 0
Hi

For the single values from the bracketed values try -

In H23 -

Code:
=VALUE(MID($G23,FIND("-",$G23)-2,2))
and I23

Code:
=VALUE(MID($G23,FIND("-",$G23)+1,2))

hth

Mike
 
Last edited:
Upvote 0
Sorry to be a pain everyone but I've found additional data that I need...

This is the last one as I've then got everything I need...

How would I take the following data and slap that in the six highlighted columns please...

Excel Workbook
GHIJKLMN
11DataData
12Rubin Kazan vs CSKA Moscow(1 : 2, 3 : 4)Rubin KazanCSKA Moscow1234
Sheet6
 
Upvote 0
If the format of the numbers remain same (i.e. 1st number right next to opening bracket, 2nd number just before comma, 3rd number is the 2nd character from the comma and 4th number is just before closing brackets) and both names contain vs between them... then try
Excel Workbook
GHIJKLMN
11DataData
12Rubin Kazan vs CSKA Moscow(1 : 2, 3 : 4)Rubin KazanCSKA Moscow1234
Sheet1
Excel 2003
Cell Formulas
RangeFormula
I12=LEFT(G12,FIND("vs",G12)-2)
J12=MID(G12,FIND("vs",G12)+3,LEN(G12))
K12=VALUE(MID(H12,2,1))
L12=VALUE(MID(H12,FIND(",",H12)-1,1))
M12=VALUE(MID(H12,FIND(",",H12)+2,1))
N12=MID(H12,LEN(H12)-1,1)
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
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