UDF to Extract Numeric Errors After 9 Digits

[DeltaBlue]

Found the following UDF (done by jindon 2 - 3 yrs ago) to Extract Numeric Digits from an AlphaNumeric Cell. It works perfectly unless there's more than 9 numeric charactors in the Cell, in which case I get a "Value" error.

------------------------------------------
Function istril(txt As String) As Long
With CreateObject("VBScript.RegExp")
.Pattern = "\D+"
.Global = True
istril = .Replace(txt,"")
End With
End Function
------------------------------------------
Cell A1 = hj4.g6j56e76,h-j34w&k5
=istril(A1) correctly gives the answer: 465676345, but if there's a 10th numeric charactor in Cell A1, I get a "Value" Error.

Any simple adjustment to UDF to eliminate the Value Error if more than 9 digits in the string (I sometimes have 20 or more numeric digits, and the alphanumeric strings can be over a 100 charactors)?

If no fix to the UDF above, a UDF (or some tricky combination of standard functions) that Counts the Number of Numeric Charactors in an AlphaNumeric Cell would work just as well for me.

If no easy fix, no worries as I can get most of what I need via jindon's UDF (that I show above), as I can use the Value Error to know if there's more than 9 numeric charactors. I'd like to know if there's more than 15 numeric charactors, but most that have over 9 also have more than 15.

Thanks All,
Larry

 

[Ruddles]

Change the first line to:-

Function istril(txt As String) As Double

 

[DeltaBlue]

Perfect Ruddles!

THANKYOU!

 

[pgc01]

Any simple adjustment to UDF to eliminate the Value Error if more than 9 digits in the string (I sometimes have 20 or more numeric digits, and the alphanumeric strings can be over a 100 charactors)?

Hi

That's not possible with a number because a cell value has a maximum of 15 significant digits.

If you need more, you can get them as a string. Replace the function declaration with:

Function istril(txt As String) As String

 

[Ruddles]

Ah, good point! Agreed.

 

[DeltaBlue]

I do see that there is no error when I go beyond 15 digits, but the 16th digit is a zero instead of the actual 16th digit.

It works fine for what I need, as if more than 15 digits I'm simply going to have the macro remove all of the digits.