Single digit number is being read as a 2 digit number.

[duteberta]

I'm creating an NFL tracker in Excel. I want to enter scores each week in cells in the following format: "6-24". However when I reference that cell later with a formula trying to get the first half of that score it reads it as "60" instead of "6". It's reading the 24 part fine but I cannot get it to read the first part as "6".

Is there a way to fix this without me typing in: "06-24"?

 

[etaf]

how are you referening ?
to get 6
then
=textbefore(cell, "-")*1
and
=textafter(cell, "-")*1

Book2
	A	B	C
1
2	6-24	6	24
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TEXTBEFORE(A2,"-")*1
C2	C2	=TEXTAFTER(A2,"-")*1

 

[Vogateer]

Can you give us an example of the formula you're trying to use? Do you need them treated as numbers or as test?

 

[Scott Huish]

The column you enter those values also should be formatted as text first, otherwise Excel is going to treat those kinds of numbers as a date.

 

[duteberta]

It works after adding "*1"- thanks etaf

rankings.xlsx
	A	B	C	D
1	SCORE	WIN	LOSS	TIE
2	6-24	0	1	0
test

Cell Formulas
Range	Formula
B2	B2	=IF(TEXTBEFORE($A$2,"-")*1>TEXTAFTER($A$2,"-")=1,1,0)
C2	C2	=IF(TEXTBEFORE($A$2,"-")*1<TEXTAFTER($A$2,"-")*1,1,0)
D2	D2	=IF(TEXTBEFORE($A$2,"-")*1=TEXTAFTER($A$2,"-")*1,1,0)

 

[Scott Huish]

The IF isn't really necessary, doing math on TRUE/FALSE values coerces them to 1 and 0 (often -- or *1)

Your first one with an =1 seems weird though.

=--(--(TEXTBEFORE($A$2,"-"))>--(TEXTAFTER($A$2,"-")))
=--(--(TEXTBEFORE($A$2,"-"))<--(TEXTAFTER($A$2,"-")))
=--(--(TEXTBEFORE($A$2,"-"))=--(TEXTAFTER($A$2,"-")))