Numerical data 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

 

[Delaware Economics]

Yes,
In the cell in which you wish to extract that data, simply type
=right(a1,6), meaning that excel will look in that cell and from the right
extract all characters from the 6th one in. You can also use the left
function; =left(cell,#_characters). They work precisely the same just
counting from different directions.

 

[Rick Rothstein \(MVP - VB\)]

You have a question to answer about your posting over in the newsgroup you
posted to.

Please consider the following for your future questions...

From a post by Jeff Johnson:

"You have posted this question individually to multiple groups.
This is called Multiposting and it's BAD. Replies made in one
group will not be visible in the other groups, which may cause
multiple people to respond to your question with the same answer
because they didn't know someone else had already done it. This
is a waste of time.

If you MUST post your message to multiple groups, post a single
message and select all the groups (or type their names manually
in the Newsgroups field, separated by commas) in which you want
it to be seen. This is called Crossposting and when used properly
it is GOOD."

Some additional comment previously posted by me:

From a post by Jeff Johnson over in the compiled VB newsgroups:

"You have posted this question individually to multiple groups.
This is called Multiposting and it's BAD. Replies made in one
group will not be visible in the other groups, which may cause
multiple people to respond to your question with the same answer
because they didn't know someone else had already done it. This
is a waste of time.

If you MUST post your message to multiple groups, post a single
message and select all the groups (or type their names manually
in the Newsgroups field, separated by commas) in which you want
it to be seen. This is called Crossposting and when used properly
it is GOOD."

Some additional comment previously posted by me:

"You may not see this as a problem, but those of us who volunteer answering
questions on newsgroups do see it as a problem. You can't imagine how
annoying it is for a volunteer to read a question, research background
material, test sample code and then formulate and post an answer to the
original question only to go to another newsgroup and find the question
posted and ALREADY answered over there. On top of that, if you cross-post
your question, all of the readers in all the newsgroups it is cross-posted
to see both the original question and all of the answers given to it. This
is beneficial to you because then we can add additional material to, add
clarification to, as well as add additional examples to an answer you have
received previously... that means you end up with a more complete solution
to your problem. This is a win-win situation for all of us."

Rick

 

[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

 

[Rick Rothstein \(MVP - VB\)]

You have a question to answer about your posting over in the **OTHER**

newsgroup you posted to.

Rick

 

[Teethless mama]

This one will work for you

=IF(OR(RIGHT(A1,4)="help",ISERROR(FIND("help",A1))),SUBSTITUTE(A1,"help",""),REPLACE(A1,1,FIND("help",A1)+3,""))