VALUE / VAL

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

Dies this help:
=IF(ISERROR(VALUE(LEFT(A1,1))),0,VALUE(LEFT(A1,1)))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Vsn" <vsn at hotmail> wrote in message
news:%[email protected]...
> 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
>

Bernard,

Thx, for your quick reply, but what happens with "67V".

Ludovic

"Bernard Liengme" <[email protected]> wrote in message
news:[email protected]...
> Dies this help:
> =IF(ISERROR(VALUE(LEFT(A1,1))),0,VALUE(LEFT(A1,1)))
> best wishes
> --
> Bernard V Liengme
> www.stfx.ca/people/bliengme
> remove caps from email
>
> "Vsn" <vsn at hotmail> wrote in message
> news:%[email protected]...
>> 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
>>
>
>

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


"Vsn" wrote:

> 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
>
>
>

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


"Ron Coderre" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> "Vsn" wrote:
>
>> 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
>>
>>
>>