Thread: Formula with Text

I have a spreadsheet that contains data in a cell that is formatted like:

3 for 50

It will always be coded like that, no other format. How do I get excel to return the number 3, but not the for 50 in a new cell? Is there a way I can tell Excel to return the number before the word for?

Thanks.

Good morning kingsolo

Do you want to return the number as a value, so it can be used in other formulae? If so use this formula :
=VALUE(LEFT(A1,FIND("for",A1,1)-2))
(assumes your data starts in A1.

Otherwise, use this :
=LEFT(A1,FIND("for",A1,1)-2)

HTH

DominicB

Thanks... worke like a charm.