Only numbers formula

[Thyag]

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

 

[Rick Rothstein \(MVP - VB\)]

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

Do the entries always start off with 6 digits, or did you just take a short
cut when posting your question?

Rick

 

[Thyag]

Do the entries always start off with 6 digits, or did you just take a short
cut when posting your question?

Rick

I have just taken short cut, but the series will always will be random.

 

[Sandy Mann]

I can't do it in a formula but I may be able to get you one. I'll give you
a UDF solution and someone will be along in a minute to suggest a formula
answer. <g>

Copy this Function and paste it into a normal module:

Function EndBit(r As Range)
Application.Volatile
If r Is Nothing Then Exit Function
For x = Len(r) To 1 Step -1
If Asc(Mid(r, x, 1)) > 57 _
Or Asc(Mid(r, x, 1)) < 48 Then
s = x + 1
Exit For
End If
If x = 1 Then s = x
Next x
EndBit = Mid(r, s, 255)
End Function

Then enter in the spreadsheet:

=EndBit(A1)

with the entry in A1 it will return return only the numbers at the end of
the entry in A1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

That's strange, I could have sworn that I tested it and it worked for an
empty cell but it does not.

Change the line:

If r Is Nothing Then Exit Function

to:

If r ="" Then Exit Function

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

You should consider declaring your function as returning a String, otherwise
it returns 0 if the cell it references is empty. Oh, and your function
returns all digits after the text... the OP asked for the last 7 or less
digits.

Here is my slightly shorter UDF for this question...

Function EndBit(R As Range) As String
Application.Volatile
If R Like "*[0-9]" Then EndBit = StrReverse(Val(StrReverse(Right$(R, 7))))
End Function

Rick

 

[Sandy Mann]

Thank you for the information Rick. I did not declare the function as
returning a string because I assumed I was returning numbers and I never
even noticed that the return was right aligned.

Unfortunately I cannot run your elegant code because I am the poor cousin
who only has XL97 - and lucky to have that.

By the time that I was writing the code I had completely forgotten about the
last 7 digits ot fewer - short term memory loss!

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

You should consider declaring your function as returning a String,
otherwise it returns 0 if the cell it references is empty. Oh, and your
function returns all digits after the text... the OP asked for the last 7
or less digits.

Here is my slightly shorter UDF for this question...

Function EndBit(R As Range) As String
Application.Volatile
If R Like "*[0-9]" Then EndBit = StrReverse(Val(StrReverse(Right$(R,
7))))
End Function

Rick

That's strange, I could have sworn that I tested it and it worked for an
empty cell but it does not.

Change the line:

If r Is Nothing Then Exit Function

to:

If r ="" Then Exit Function

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

even noticed that the return was right aligned.

Of course you know that I meant wasn't right aligned!

--


Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

Thank you for the information Rick. I did not declare the function as
returning a string because I assumed I was returning numbers and I never
even noticed that the return was right aligned.

Unfortunately I cannot run your elegant code because I am the poor cousin
who only has XL97 - and lucky to have that.

By the time that I was writing the code I had completely forgotten about
the last 7 digits ot fewer - short term memory loss!

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

You should consider declaring your function as returning a String,
otherwise it returns 0 if the cell it references is empty. Oh, and your
function returns all digits after the text... the OP asked for the last 7
or less digits.

Here is my slightly shorter UDF for this question...

Function EndBit(R As Range) As String
Application.Volatile
If R Like "*[0-9]" Then EndBit = StrReverse(Val(StrReverse(Right$(R,
7))))
End Function

Rick

That's strange, I could have sworn that I tested it and it worked for an
empty cell but it does not.

Change the line:

If r Is Nothing Then Exit Function

to:

If r ="" Then Exit Function

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


I can't do it in a formula but I may be able to get you one. I'll give
you
a UDF solution and someone will be along in a minute to suggest a
formula
answer. <g>

Copy this Function and paste it into a normal module:

Function EndBit(r As Range)
Application.Volatile
If r Is Nothing Then Exit Function
For x = Len(r) To 1 Step -1
If Asc(Mid(r, x, 1)) > 57 _
Or Asc(Mid(r, x, 1)) < 48 Then
s = x + 1
Exit For
End If
If x = 1 Then s = x
Next x
EndBit = Mid(r, s, 255)
End Function

Then enter in the spreadsheet:

=EndBit(A1)

with the entry in A1 it will return return only the numbers at the end
of
the entry in A1

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


On Aug 13, 2:45 pm, "Rick Rothstein \(MVP - VB\)"
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".

Do the entries always start off with 6 digits, or did you just take a
short
cut when posting your question?

Rick

I have just taken short cut, but the series will always will be
random.

 

[Pete_UK]

This formula seems to do what the OP wanted:

=LEFT(IF(LEN(SUBSTITUTE(A1,"help",""))=LEN(A1),A1,IF(RIGHT(A1,4)="help",LEFT(A1,LEN(A1)-4),MID(A1,FIND("help",A1)+4,255))),
7)

Limiting the return to 7 characters maximum, it returns the numbers to
the right of "help" (if any) or to the left of "help" if there are
none.

Hope this "helps" <bg>

Pete

Thank you for the information Rick. I did not declare the function as
returning a string because I assumed I was returning numbers and I never
even noticed that the return was right aligned.

Unfortunately I cannot run your elegant code because I am the poor cousin
who only has XL97 - and lucky to have that.

By the time that I was writing the code I had completely forgotten about the
last 7 digits ot fewer - short term memory loss!

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

message

You should consider declaring your function as returning a String,
otherwise it returns 0 if the cell it references is empty. Oh, and your
function returns all digits after the text... the OP asked for the last 7
or less digits.

Here is my slightly shorter UDF for this question...

Function EndBit(R As Range) As String
Application.Volatile
If R Like "*[0-9]" Then EndBit = StrReverse(Val(StrReverse(Right$(R,
7))))
End Function

Rick

That's strange, I could have sworn that I tested it and it worked for an
empty cell but it does not.
Change the line:
If r Is Nothing Then Exit Function
to:
If r ="" Then Exit Function
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
I can't do it in a formula but I may be able to get you one. I'll give
you
a UDF solution and someone will be along in a minute to suggest a
formula
answer. <g>
Copy this Function and paste it into a normal module:
Function EndBit(r As Range)
Application.Volatile
If r Is Nothing Then Exit Function
For x = Len(r) To 1 Step -1
If Asc(Mid(r, x, 1)) > 57 _
Or Asc(Mid(r, x, 1)) < 48 Then
s = x + 1
Exit For
End If
If x = 1 Then s = x
Next x
EndBit = Mid(r, s, 255)
End Function
Then enter in the spreadsheet:
=EndBit(A1)
with the entry in A1 it will return return only the numbers at the end
of
the entry in A1
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
On Aug 13, 2:45 pm, "Rick Rothstein \(MVP - VB\)"
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".
Do the entries always start off with 6 digits, or did you just take a
short
cut when posting your question?
Rick
I have just taken short cut, but the series will always will be random.- Hide quoted text -

- Show quoted text -

 

[Sandy Mann]

I knew that there would be someone along with a formula answer <g>

I have been assuming that the "help" may not always be the letters - for not
particular reason other than seen posters post consistant data, (like the
OP's 6 leading digits), only to say when a solution is posted that the
entries will not always be the same format.

We will just have to wait and see what the OP says.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

This formula seems to do what the OP wanted:

=LEFT(IF(LEN(SUBSTITUTE(A1,"help",""))=LEN(A1),A1,IF(RIGHT(A1,4)="help",LEFT(A1,LEN(A1)-4),MID(A1,FIND("help",A1)+4,255))),
7)

Limiting the return to 7 characters maximum, it returns the numbers to
the right of "help" (if any) or to the left of "help" if there are
none.

Hope this "helps" <bg>

Pete

Thank you for the information Rick. I did not declare the function as
returning a string because I assumed I was returning numbers and I never
even noticed that the return was right aligned.

Unfortunately I cannot run your elegant code because I am the poor cousin
who only has XL97 - and lucky to have that.

By the time that I was writing the code I had completely forgotten about
the
last 7 digits ot fewer - short term memory loss!

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

message

You should consider declaring your function as returning a String,
otherwise it returns 0 if the cell it references is empty. Oh, and your
function returns all digits after the text... the OP asked for the last
7
or less digits.

Here is my slightly shorter UDF for this question...

Function EndBit(R As Range) As String
Application.Volatile
If R Like "*[0-9]" Then EndBit = StrReverse(Val(StrReverse(Right$(R,
7))))
End Function

Rick

That's strange, I could have sworn that I tested it and it worked for
an
empty cell but it does not.

Change the line:

If r Is Nothing Then Exit Function

If r ="" Then Exit Function
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

I can't do it in a formula but I may be able to get you one. I'll
give
you
a UDF solution and someone will be along in a minute to suggest a
formula
answer. <g>

Copy this Function and paste it into a normal module:

Function EndBit(r As Range)
Application.Volatile
If r Is Nothing Then Exit Function
For x = Len(r) To 1 Step -1
If Asc(Mid(r, x, 1)) > 57 _
Or Asc(Mid(r, x, 1)) < 48 Then
s = x + 1
Exit For
End If
If x = 1 Then s = x
Next x
EndBit = Mid(r, s, 255)
End Function

Then enter in the spreadsheet:

with the entry in A1 it will return return only the numbers at the
end
of
the entry in A1
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

On Aug 13, 2:45 pm, "Rick Rothstein \(MVP - VB\)"
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".

Do the entries always start off with 6 digits, or did you just take
a
short
cut when posting your question?

I have just taken short cut, but the series will always will be
random.- Hide quoted text -

- Show quoted text -

 

[Pete_UK]

Yes, I've assumed that there are numbers, possibly followed by the
word "help" and that possibly followed by more numbers. There are many
other interpretations that could be put on the OP's examples.

Pete

I knew that there would be someone along with a formula answer <g>

I have been assuming that the "help" may not always be the letters - for not
particular reason other than seen posters post consistant data, (like the
OP's 6 leading digits), only to say when a solution is posted that the
entries will not always be the same format.

We will just have to wait and see what the OP says.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

This formula seems to do what the OP wanted:

Limiting the return to 7 characters maximum, it returns the numbers to
the right of "help" (if any) or to the left of "help" if there are
none.

Hope this "helps" <bg>

Pete

Thank you for the information Rick. I did not declare the function as
returning a string because I assumed I was returning numbers and I never
even noticed that the return was right aligned.
Unfortunately I cannot run your elegant code because I am the poor cousin
who only has XL97 - and lucky to have that.
By the time that I was writing the code I had completely forgotten about
the
last 7 digits ot fewer - short term memory loss!
--
Regards,
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
message
You should consider declaring your function as returning a String,
otherwise it returns 0 if the cell it references is empty. Oh, and your
function returns all digits after the text... the OP asked for the last
7
or less digits.
Here is my slightly shorter UDF for this question...
Function EndBit(R As Range) As String
Application.Volatile
If R Like "*[0-9]" Then EndBit = StrReverse(Val(StrReverse(Right$(R,
7))))
End Function
Rick
That's strange, I could have sworn that I tested it and it worked for
an
empty cell but it does not.
Change the line:
If r Is Nothing Then Exit Function
to:
If r ="" Then Exit Function
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
I can't do it in a formula but I may be able to get you one. I'll
give
you
a UDF solution and someone will be along in a minute to suggest a
formula
answer. <g>
Copy this Function and paste it into a normal module:
Function EndBit(r As Range)
Application.Volatile
If r Is Nothing Then Exit Function
For x = Len(r) To 1 Step -1
If Asc(Mid(r, x, 1)) > 57 _
Or Asc(Mid(r, x, 1)) < 48 Then
s = x + 1
Exit For
End If
If x = 1 Then s = x
Next x
EndBit = Mid(r, s, 255)
End Function
Then enter in the spreadsheet:
=EndBit(A1)
with the entry in A1 it will return return only the numbers at the
end
of
the entry in A1
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
On Aug 13, 2:45 pm, "Rick Rothstein \(MVP - VB\)"
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".
Do the entries always start off with 6 digits, or did you just take
a
short
cut when posting your question?
Rick
I have just taken short cut, but the series will always will be
random.- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

You should consider declaring your function as returning a String, otherwise
it returns 0 if the cell it references is empty. Oh, and your function
returns all digits after the text... the OP asked for the last 7 or less
digits.

Actually, he asked for the last 7 or less digits.

Look at his example 3:

3 - if A1 consists 112233help - I need "112233".

Your UDF, and Sandy's, (on my machine) returns a blank in that example.


--ron

 

[Rick Rothstein \(MVP - VB\)]

This formula seems to do what the OP wanted:

=LEFT(IF(LEN(SUBSTITUTE(A1,"help",""))=LEN(A1),A1,IF(RIGHT(A1,4)="help",LEFT(A1,LEN(A1)-4),MID(A1,FIND("help",A1)+4,255))),
7)

Limiting the return to 7 characters maximum, it returns the numbers to
the right of "help" (if any) or to the left of "help" if there are
none.

Although my original question to the OP dealt with the number of leading
digits, I got the impression when he said "the series will always will be
random" that any text in the string of characters would not necessarily
spell out the word "help". If I am correct, then the following formula
should do what the OP wants...

=IF(ISNUMBER(--RIGHT(A1,7)),RIGHT(A1,7),IF(ISNUMBER(--RIGHT(A1,6)),RIGHT(A1,6),IF(ISNUMBER(--RIGHT(A1,5)),RIGHT(A1,5),IF(ISNUMBER(--RIGHT(A1,4)),RIGHT(A1,4),IF(ISNUMBER(--RIGHT(A1,3)),RIGHT(A1,3),IF(ISNUMBER(--RIGHT(A1,2)),RIGHT(A1,2),RIGHT(A1,--ISNUMBER(--A1))))))))

Yeah, I know... not very elegant... but it does seem to work.<g>

Rick

 

[Rick Rothstein \(MVP - VB\)]

You should consider declaring your function as returning a String,

Actually, he asked for the last 7 or less digits.

Look at his example 3:

3 - if A1 consists 112233help - I need "112233".

Your UDF, and Sandy's, (on my machine) returns a blank in that example.

Good eye!!! I totally missed that condition. Well, it's back to the drawing
boards again.<g>

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

Based on a comment by Ron Rosenfelf, you can ignore the entire previous
(lengthy) thread as everything it in has an error. Here is my revised UDF
which, unless I made another misreading, will do what you asked...

Function EndBit(R As Range) As String
Application.Volatile
If R Like "*[0-9]" Then
EndBit = StrReverse(Val(StrReverse(Right$(R, 7))))
ElseIf R Like "[0-9]*" Then
EndBit = Right(Val(R), 7)
Else
EndBit = ""
End If
End Function

You would still put it in a module and call it directly from the spreadsheet
as discuss in that other thread. By the way, you didn't say what you wanted
to do if there were more than 7 digits in front of the text and there were
no digits after it. I assumed you still wanted the last 7 digits.

Rick

 

[Rick Rothstein \(MVP - VB\)]

Based on a comment by Ron Rosenfelf, you can ignore the entire previous

(lengthy) thread as everything it in has an error. Here is my revised UDF
which, unless I made another misreading, will do what you asked...

Function EndBit(R As Range) As String
Application.Volatile
If R Like "*[0-9]" Then
EndBit = StrReverse(Val(StrReverse(Right$(R, 7))))
ElseIf R Like "[0-9]*" Then
EndBit = Right(Val(R), 7)
Else
EndBit = ""
End If
End Function

NOPE, forget this one... it may not work correctly when there are leading
and/or trailing zeroes.

Rick

 

[Ron Rosenfeld]

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

A minor change, prompted by Richard's message:

The following will include leading zero's in the digits returned.

========================================
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}(?=\D|$)"

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

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

End Function
--ron

 

[Ron Rosenfeld]

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

Sorry, the poster who mentioned leading zeros is Rick, not Richard.
--ron

 

[Rick Rothstein \(MVP - VB\)]

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

Okay, **this** UDF works... finally.<g>

Function EndBit(R As Range) As String
Application.Volatile
Dim Delimiter As String
Dim LeftRight() As String
If Not R.Text Like "*[!0-9]*" Then
EndBit = Right(R.Text, 7)
Else
Delimiter = R.Text
For x = 0 To 9
Delimiter = Replace(Delimiter, CStr(x), "")
Next
LeftRight = Split(R.Text, Delimiter)
EndBit = Right(LeftRight(-(Len(LeftRight(1)) > 0)), 7)
End If
End Function

Place it in a Module and then call it from the worksheet like any other
function. For example, =EndBit(A1).

Rick