Equivalent Of VAL

[davidm]

In VBA two cells containing alphanumeric strings like "123abc" can b
subjected to evaluation via the use of VAL. Thus, with "97asd" in A
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 an
VALUE
Worksheetfunctions failed me in this regard.

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

 

[Tom Ogilvy]

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.

 

[davidm]

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

 

[Chip Pearson]

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

 

[Tom Ogilvy]

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.