Strings...extracting and altering data...

[How_Do_I]

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
	H	I	J	K	L
14	17.03.2006	17-Mar-06			Date
15	( 0-0 )	0	0		Score to 2 Columns
16	1-0	1	0		Score to 2 Columns
17	( 0-0 )	0 - 0			Score to Single Column
18	1-0	1 - 0			Score to Single Column
Sheet3

 

[Special-K99]

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

 

[How_Do_I]

Hello…
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
The values in column H have been downloaded so I want to get them in a format that I can work with…
<o> </o>
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> </o>
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> </o>
H16 is nearly the same as above only the data I downloaded isn’t in brackets…
<o> </o>
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…

 

[How_Do_I]

H15 =SUBSTITUTE(SUBSTITUTE(H15,"(",""),")","") has removed the brackets...

 

[How_Do_I]

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
	G	H	I	J	K
23	( 1-0 )	1-0	1 - 0	1	0
Sheet3

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

 

[ukmikeb]

Hi

For the single values from the bracketed values try -

In H23 -

=VALUE(MID($G23,FIND("-",$G23)-2,2))

and I23

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

hth

Mike

 

[Sandeep Warrier]

To convert the date try

=DATEVALUE(SUBSTITUTE(A1,".","/"))

with cell formatted as date.

 

[How_Do_I]

Thank you both for those solutions...

 

[How_Do_I]

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
	G	H	I	J	K	L	M	N
11	Data	Data
12	Rubin Kazan vs CSKA Moscow	(1 : 2, 3 : 4)	Rubin Kazan	CSKA Moscow	1	2	3	4
Sheet6

 

[Sandeep Warrier]

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
	G	H	I	J	K	L	M	N
11	Data	Data
12	Rubin Kazan vs CSKA Moscow	(1 : 2, 3 : 4)	Rubin Kazan	CSKA Moscow	1	2	3	4
Sheet1

Excel 2003

Cell Formulas
Range	Formula
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)