Alphanumeric

Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
And so on. Now I want to extract number from this alphanumeric. Once I have
seen a formula of (SUBSITUTE) in a site it was so simple that any body can
use that formula. Its very short formula. Spouse a1:a1500 has alphanumeric
data put the formula in B1 and drag this formula to B7. In B7 You will find
the only number.
If you can help me in this matter
It Will Be most Appriciate

Regards
Hardeep

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1

There are multiple numbers in there, is the result of the first

125
121 or
125589121

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"hardeep via OfficeKB.com" u44683@uwe wrote in message
news:87b52aba76a22@uwe...
Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
And so on. Now I want to extract number from this alphanumeric. Once I
have
seen a formula of (SUBSITUTE) in a site it was so simple that any body can
use that formula. Its very short formula. Spouse a1:a1500 has alphanumeric
data put the formula in B1 and drag this formula to B7. In B7 You will
find
the only number.
If you can help me in this matter
It Will Be most Appriciate

Regards
Hardeep

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1

Hi,

I can't decide form your post exactly what each string (or is it one string)
looks like. Right click your sheet tab, view code and paste this in and run
it. It works on a1:a100 which you can change and outputs the numbers in
column B

Sub extractnumbers()
Set RegExp = CreateObject("vbscript.RegExp")
RegExp.Global = True
RegExp.Pattern = "\d"
Set myrange = ActiveSheet.Range("a1:a100") 'change to suit
For Each c In myrange
Outstring = ""
Set Collection = RegExp.Execute(c.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
c.Offset(0, 1) = Outstring
Next
End Sub


Mike



"hardeep via OfficeKB.com" wrote:

Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
And so on. Now I want to extract number from this alphanumeric. Once I have
seen a formula of (SUBSITUTE) in a site it was so simple that any body can
use that formula. Its very short formula. Spouse a1:a1500 has alphanumeric
data put the formula in B1 and drag this formula to B7. In B7 You will find
the only number.
If you can help me in this matter
It Will Be most Appriciate

Regards
Hardeep

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1

Just so we know exactly what you are looking for, what numerical result did
you want to appear in B7 for this piece of data?

ADEDO125ADSD589ADF121

Rick


"hardeep via OfficeKB.com" u44683@uwe wrote in message
news:87b52aba76a22@uwe...
Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
And so on. Now I want to extract number from this alphanumeric. Once I
have
seen a formula of (SUBSITUTE) in a site it was so simple that any body can
use that formula. Its very short formula. Spouse a1:a1500 has alphanumeric
data put the formula in B1 and drag this formula to B7. In B7 You will
find
the only number.
If you can help me in this matter
It Will Be most Appriciate

Regards
Hardeep

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1

I want Numeric characters for example: 125589121,12556545,1545521245642 and
so on.

Thank In Advance

Hardeep

Bob Phillips wrote:
There are multiple numbers in there, is the result of the first

125
121 or
125589121

Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
[quoted text clipped - 11 lines]
Regards
Hardeep

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1

I want Numeric characters for example: 125589121,12556545,1545521245642 and
so on.

Thank In Advance

Hardeep

Rick Rothstein (MVP - VB) wrote:
Just so we know exactly what you are looking for, what numerical result did
you want to appear in B7 for this piece of data?

ADEDO125ADSD589ADF121

Rick

Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
[quoted text clipped - 11 lines]
Regards
Hardeep

--
Message posted via http://www.officekb.com

Perhaps you may prefer it as a UDF. This must go in a 'General Module'

Function extractnumbers(rng As String)
Set RegExp = CreateObject("vbscript.RegExp")
RegExp.Global = True
RegExp.Pattern = "\d"
For Each RegMatch In RegExp.Execute(rng)
extractnumbers = extractnumbers & RegMatch
Next
End Function

call with
=extractnumbers(a1)
where A1 contains you string

Mike

"Mike H" wrote:

Hi,

I can't decide form your post exactly what each string (or is it one string)
looks like. Right click your sheet tab, view code and paste this in and run
it. It works on a1:a100 which you can change and outputs the numbers in
column B

Sub extractnumbers()
Set RegExp = CreateObject("vbscript.RegExp")
RegExp.Global = True
RegExp.Pattern = "\d"
Set myrange = ActiveSheet.Range("a1:a100") 'change to suit
For Each c In myrange
Outstring = ""
Set Collection = RegExp.Execute(c.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
c.Offset(0, 1) = Outstring
Next
End Sub


Mike



"hardeep via OfficeKB.com" wrote:

Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
And so on. Now I want to extract number from this alphanumeric. Once I have
seen a formula of (SUBSITUTE) in a site it was so simple that any body can
use that formula. Its very short formula. Spouse a1:a1500 has alphanumeric
data put the formula in B1 and drag this formula to B7. In B7 You will find
the only number.
If you can help me in this matter
It Will Be most Appriciate

Regards
Hardeep

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1

Mike's UDF looks the way to go then.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"hardeep via OfficeKB.com" u44683@uwe wrote in message
news:87b8780eb72c9@uwe...
I want Numeric characters for example: 125589121,12556545,1545521245642 and
so on.

Thank In Advance

Hardeep

Bob Phillips wrote:
There are multiple numbers in there, is the result of the first

125
121 or
125589121

Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
[quoted text clipped - 11 lines]
Regards
Hardeep

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1

On Sat, 26 Jul 2008 04:31:22 GMT, "hardeep via OfficeKB.com" u44683@uwe
wrote:

Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
And so on. Now I want to extract number from this alphanumeric. Once I have
seen a formula of (SUBSITUTE) in a site it was so simple that any body can
use that formula. Its very short formula. Spouse a1:a1500 has alphanumeric
data put the formula in B1 and drag this formula to B7. In B7 You will find
the only number.
If you can help me in this matter
It Will Be most Appriciate

Regards
Hardeep


For a worksheet function (formula) solution:

(from Harlan Grove):

First, create a NAMEd Formula

Names in Workbook: Seq
Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

This ARRAY FORMULA
(committed with CTRL+SHIFT+ENTER, instead of just ENTER)
removes ALL non-numerics from a string.

B1:
=SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)*
10^MMULT(-(seqTRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))

===================================
For a UDF

alt-F11 opens the VBEditor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF:

=Digits(str) where str is either the quoted string, or a cell reference
containing your string. e.g. =Digits(A1) will return all the digits in A1.

If you wish this returned as a "Number" rather than as "Text", precede the
formula with a double unary:

=--Digits(A1)

=====================================
Option Explicit
Function Digits(str As String)
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D+"
Digits = re.Replace(str, "")
End Function
===========================
--ron

Thanks Sir

Hardeep kanwar

Ron Rosenfeld wrote:
Dear sir
I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
[quoted text clipped - 9 lines]
Regards
Hardeep

For a worksheet function (formula) solution:

(from Harlan Grove):

First, create a NAMEd Formula

Names in Workbook: Seq
Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

This ARRAY FORMULA
(committed with CTRL+SHIFT+ENTER, instead of just ENTER)
removes ALL non-numerics from a string.

B1:
=SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)*
10^MMULT(-(seqTRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))

===================================
For a UDF

alt-F11 opens the VBEditor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this UDF:

=Digits(str) where str is either the quoted string, or a cell reference
containing your string. e.g. =Digits(A1) will return all the digits in A1.

If you wish this returned as a "Number" rather than as "Text", precede the
formula with a double unary:

=--Digits(A1)

=====================================
Option Explicit
Function Digits(str As String)
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D+"
Digits = re.Replace(str, "")
End Function
===========================
--ron

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200807/1