Strings...extracting and altering data...

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,781
Office Version
2010
Platform
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,343
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

Well-known Member
Joined
Oct 23, 2009
Messages
1,781
Office Version
2010
Platform
Windows
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…
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,781
Office Version
2010
Platform
Windows
H15 =SUBSTITUTE(SUBSTITUTE(H15,"(",""),")","") has removed the brackets...
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,781
Office Version
2010
Platform
Windows
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?
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
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:

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
To convert the date try

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

with cell formatted as date.
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,781
Office Version
2010
Platform
Windows
:) Thank you both for those solutions...
 

How_Do_I

Well-known Member
Joined
Oct 23, 2009
Messages
1,781
Office Version
2010
Platform
Windows
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
 

Sandeep Warrier

Board Regularmmmm Pizza
Joined
Oct 31, 2008
Messages
2,672
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)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,950
Messages
5,508,324
Members
408,678
Latest member
ripperbolt

This Week's Hot Topics

Top