Equivalent Of VAL

In VBA two cells containing alphanumeric strings like "123abc" can be subjected to evaluation via the use of VAL. Thus, with "97asd" in A1 and "54jkm" in A2,
Val(Range("a1"))-Val(Range("a2")) yields 43 ~ (97 minus 23).

Is there a Worksheetfunction that perfoms such role? Both the N and VALUE
Worksheetfunctions failed me in this regard.

=N(A1)-N(A2) --> 0 while VALUE(A1)-VALUE(A2) --> #NAME error.

No, there is no equivalent function. If you know it will only be the 2
leftmost numbers

=Left(A1,2) - Left(A2,2)
would be such an obvious solution that I am sure that is not the case.

--
Regards,
Tom Ogilvy


"davidm" <davidm.1su2yz_1122476797.5324@excelforum-nospam.com> wrote in
message news:davidm.1su2yz_1122476797.5324@excelforum-nospam.com...
>
> In VBA two cells containing alphanumeric strings like "123abc" can be
> subjected to evaluation via the use of VAL. Thus, with "97asd" in A1
> and "54jkm" in A2,
> Val(Range("a1"))-Val(Range("a2")) yields 43 ~ (97 minus 23).
>
> Is there a Worksheetfunction that perfoms such role? Both the N and
> VALUE
> Worksheetfunctions failed me in this regard.
>
> =N(A1)-N(A2) --> 0 while VALUE(A1)-VALUE(A2) --> #NAME error.
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=390579
>

Many thanks Tom.
Oftentimes, a confirmatory NO answer is a positive one! It sets the mind at rest.

There is no worksheet equivalent. You could write your own custom
function:

Function Val(S As String) As Variant
Val = VBA.Val(S)
End Function

Then, call this function from your worksheet cells.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"davidm" <davidm.1su2yz_1122476797.5324@excelforum-nospam.com>
wrote in message
news:davidm.1su2yz_1122476797.5324@excelforum-nospam.com...
>
> In VBA two cells containing alphanumeric strings like "123abc"
> can be
> subjected to evaluation via the use of VAL. Thus, with "97asd"
> in A1
> and "54jkm" in A2,
> Val(Range("a1"))-Val(Range("a2")) yields 43 ~ (97 minus 23).
>
> Is there a Worksheetfunction that perfoms such role? Both the
> N and
> VALUE
> Worksheetfunctions failed me in this regard.
>
> =N(A1)-N(A2) --> 0 while VALUE(A1)-VALUE(A2) --> #NAME error.
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
> http://www.excelforum.com/member.php...o&userid=20645
> View this thread:
> http://www.excelforum.com/showthread...hreadid=390579
>

Not to mislead, however, you can use a combination of functions in an array
formula to accomplish what you want to do - but that wasn't the question.
Or use a UDF as shown by Chip.

If you need the combination of functions, repost here or ask in
worksheet.functions.

--
Regards,
Tom Ogilvy

"davidm" <davidm.1su8k5_1122484183.9991@excelforum-nospam.com> wrote in
message news:davidm.1su8k5_1122484183.9991@excelforum-nospam.com...
>
> Many thanks Tom.
> Oftentimes, a confirmatory NO answer is a positive one! It sets the
> mind at rest.
>
>
> --
> davidm
> ------------------------------------------------------------------------
> davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=390579
>