Calculation error

[NikkiMc204]

Hi can some kind soul help me with a calculation error I'm getting

I'm trying to extract the first numerical value from a text string. The text "10 FTE X 1hr = X days"

So i'm trying to extract the first number "10" I don't want any of the other numbers that show after the text FTE.

I've used this formula =(IFERROR(LEFT(H4,(FIND("FTE",H4,1)-1)),"")) which extracts the number 10 fine but when I go to add up all the figures using this formula i get a zero value =sum(h4:h500)

Where am i going wrong?

 

[Craggs82]

Your formula is returning a string, you'll need to force it into an integer to allow for calculation using INT.

=(IFERROR(INT(LEFT(H4,(FIND("FTE",H4,1)-1))),""))

 

[Fluff]

Another option in-case you can have decimal numbers

=IFERROR(LEFT(H4,FIND("FTE",H4,1)-1)+0,"")

 

[Craggs82]

Excel Formula:
=IFERROR(LEFT(H4,FIND("FTE",H4,1)-1)+0,"")

ooh thats sneaky, i like it!

 

[Craggs82]

=(IFERROR(VALUE(LEFT(H4,(FIND("FTE",H4,1)-1))),""))

Is, in fact, what i should have put. I'm so used to rigidly defined variables ?‍ my bad! use Fluffs answer.

 

[NikkiMc204]

Thank you so much those formulas work perfectly

 

[Fluff]

Glad we could help & thanks for the feedback.