On Mon, 13 Aug 2007 11:32:00 -0700, Thyag wrote:

Hi Friends,

I am in requirement of a formula for extracting only last seven (or
less) numerical digits from a reference.


Ex -
1 - if A1 consists 112233help573847 - I need "573847".
2 - if A1 consists 112233help573 - I need "573".
3 - if A1 consists 112233help - I need "112233".
4 - if A1 consists 112233 - Ineed "112233".


Any help is appriciated.


Thanks,
Thyag

Not sure what you want to do if there are no digits in the string. The UDF
below will return a #NUM! error in that case, but you could modify that.

To use the UDF, enter =lastdigits(cell_ref) in some cell.

To enter the VBA, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

Then select Tools/References and "check" Microsoft VBScript Regular Expressions
5.5 from the drop down list.

See if this helps:

===========================================
Option Explicit
Function LastDigits(str As String)
'Requires setting reference to Microsoft VBScript Regular Expressions 5.5
Dim oRegex As RegExp
Dim mc As MatchCollection
Const sPattern As String = "\d{1,7}"

Set oRegex = New RegExp
oRegex.Pattern = sPattern
oRegex.Global = True

If oRegex.Test(str) = True Then
Set mc = oRegex.Execute(str)
LastDigits = CDbl(mc(mc.Count - 1))
Else
LastDigits = CVErr(xlErrNum)
End If

End Function
============================================
--ron