[Formulawiz]

If you have a cell with the values of 12-9 which reflects a win/loss record. How do you extract the value 12 and the the value 9 without including the dash. Remember at the beginning of the season you may have a record of 1-1 and then when the season progresses the values may go to 10-10. How does Excel automatically recognize the vales going to 2 digits.
I am looking for a formula such as this
=VALUE(LEFT(ARI!B44,1))
=Mid(A2,Find(“-“,A2)+1,2)
The problem with these you have to change the value from 1 to 2 at the end of formula 1 manually. I want the formula to recognize if the value has a double digit such as 10 vs. a single digit number such a 9.
Thanks

[Miz]

my reply doesn't apply to the updated post

[roasthawg]

Select the column with the data in it. Go to "data" and then select "text to columns". Select "delimited" and then place a check mark in the box marked "other". Enter in a "-" in the box and select finish. Make sure you have an empty column next to the the column that you are delimiting or it will overwrite the column.

Alternatively you can extract the text with from cell "A1" with a function like this:
=LEFT(A1,FIND("-",A1)-1)*1 (gives you the 12 wins)
=RIGHT(A1,LEN(A1)-FIND(".",A1))*1 (gives you the 9 losses)

This is with a newer version of excel but both methods should still work for you. Good luck!

[Emily_Haines]

try this.......................

=REPLACE(A1,FIND("-",A1),1,"")

[Formulawiz]

Select the column with the data in it. Go to "data" and then select "text to columns". Select "delimited" and then place a check mark in the box marked "other". Enter in a "-" in the box and select finish. Make sure you have an empty column next to the the column that you are delimiting or it will overwrite the column.

Alternatively you can extract the text with from cell "A1" with a function like this:
=LEFT(A1,FIND("-",A1)-1)*1 (gives you the 12 wins)
=RIGHT(A1,LEN(A1)-FIND(".",A1))*1 (gives you the 9 losses)

This is with a newer version of excel but both methods should still work for you. Good luck!

Thank you the formulas worked great