VALUE / VAL

B

Bernard Liengme

Dies this help:
=IF(ISERROR(VALUE(LEFT(A1,1))),0,VALUE(LEFT(A1,1)))
best wishes
 
V

Vsn

Hi all,

Can someone tell me if there is a sheet function which reacts the same as
VAL("T6") = 0, VAL("6T") = 6, VALUE will produce an error?

? VAL("6T")
6
? VAL("T6")
0

=VALUE("6T")
#VALUE!

=VALUE("T6")
#VALUE!

Eventualy I would like to see if a cell contains a number or text.

Thx,
Ludovic
 
R

Ron Coderre

Maybe one of these? They extract contiguous numbers from text.:

For a value in A1

This one returns n/a if no numbers exist in the string:
B1:
=LOOKUP(10^99,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))))

or

This one returns zero if no numbers exist in the string:
B1:
=LOOKUP(10^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Note_1: in case of text wrap, there are no spaces in either of those formulas.
Note_2: If the largest value you anticipate encountering is greater than
10^99, then use this instead: 9.99999999999999E+307

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
V

Vsn

Thx, guys, i possibly stick with a function calling VB to do the job. Just
thought that there must be a build-in function.

Ludovic
 
Top