Left Numbervalue

[creative999]

Hi

I need to pull out the number values from various sheets. I thought i could use something like this to pull out the first two numeric values (1-10)

NUMBERVALUE(LEFT(Sheet1!H2,2).

However, the problem is that some cell values contain a hyphen immediately after the number, ie '4-Start', '9-Good', '10-Complete' and using the formulae above doesnt work. I cant just pull the first numeric value as there could be a '10'.

How do i get around this and get the numeric value with our without a hyphen in the originating cell?

 

[FormR]

Hi, you could try like this..

=NUMBERVALUE(LEFT(Sheet1!H2,FIND("-",Sheet1!H2&"-")-1))

 

[Phuoc]

Try this:

=--LEFT(Sheet1!H2,FIND("-",Sheet1!H2)-1)

Or:

=LOOKUP(99,--LEFT(Sheet1!H2,{1,2}))

 

[creative999]

Hi, you could try like this..

=NUMBERVALUE(LEFT(Sheet1!H2,FIND("-",Sheet1!H2&"-")-1))

Thank you.... worked perfectly