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

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

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

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

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

Thanks Sir

Hardeep kanwar

Mike H wrote:
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

Hi,

[quoted text clipped - 33 lines]
Regards
Hardeep

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

Hi there Mike

with regard to this alphanumeric problem, i have my own version of problem

let say i have one colimn that contain:
column A
p12345
tr58967
drt45862

and i want to seperate the numbers and letters
and will look like this

column A column B column C
p12345 p 12345
tr58967 tr 58964
drt45862 drt 45862

what type of function or formula i can use to do this task

Based on your posted sample...assuming the data starts in A2.

Enter this formula in B2:

=SUBSTITUTE(A2,C2,"")

Note that this will return a TEXT value. If you want the number to be a
numeric number then try this:

=--SUBSTITUTE(A2,C2,"")

However, if your number string starts with leading 0s this will strip them
off. If you number strings are all the same length (as in your sample data)
and will have leading 0s then you can use the "--" version and then custom
number format to retain the leading 0s.

Enter this formula in C2:

=LEFT(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))

Select both B2 and C2 and copy down as needed.

--
Biff
Microsoft Excel MVP


"beejay" wrote in message
...
Hi there Mike

with regard to this alphanumeric problem, i have my own version of problem

let say i have one colimn that contain:
column A
p12345
tr58967
drt45862

and i want to seperate the numbers and letters
and will look like this

column A column B column C
p12345 p 12345
tr58967 tr 58964
drt45862 drt 45862

what type of function or formula i can use to do this task

Possible improvement...

In your sample data all the number strings are the same length. So, if
that's the case then we can use much simpler formulas.

In B2:

=LEFT(A2,LEN(A2)-5)

In C2:

=RIGHT(A2,5) (returns a TEXT number)
=--RIGHT(A2,5) (returns a NUMERIC number)

The same condition applies to leading 0s.


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Based on your posted sample...assuming the data starts in A2.

Enter this formula in B2:

=SUBSTITUTE(A2,C2,"")

Note that this will return a TEXT value. If you want the number to be a
numeric number then try this:

=--SUBSTITUTE(A2,C2,"")

However, if your number string starts with leading 0s this will strip them
off. If you number strings are all the same length (as in your sample
data) and will have leading 0s then you can use the "--" version and then
custom number format to retain the leading 0s.

Enter this formula in C2:

=LEFT(A2,LEN(A2)-SUM(LEN(A2)-LEN(SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},""))))

Select both B2 and C2 and copy down as needed.

--
Biff
Microsoft Excel MVP


"beejay" wrote in message
...
Hi there Mike

with regard to this alphanumeric problem, i have my own version of
problem

let say i have one colimn that contain:
column A
p12345
tr58967
drt45862

and i want to seperate the numbers and letters
and will look like this

column A column B column C
p12345 p 12345
tr58967 tr 58964
drt45862 drt 45862

what type of function or formula i can use to do this task

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

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

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(-(seq<TRANSPOSE(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(-(seq<TRANSPOSE(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

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/300...-10423159.html

....then use this formula

=REGEX.SUBSTITUTE(A1,"[A-Z,a-z]","")


"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

On Sat, 26 Jul 2008 07:21:00 -0700, Teethless mama
wrote:

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/300...-10423159.html

...then use this formula

=REGEX.SUBSTITUTE(A1,"[A-Z,a-z]","")


Simpler and more inclusive:

=REGEX.SUBSTITUTE(A1,"\D+","")
--ron

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


Here is another formula you may try:

=MID(SUMPRODUCT(-
-MID("01"&A1,SMALL((ROW(A1:A300)-1)*ISNUMBER(-MID("01"&A1,ROW(A1:A300),1)),ROW(A1:A300))+1,1),10 ^(300-ROW(A1:A300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 15 digits in the input string.
(Following digits will be shown as zeroes.)

Maybe of no pratical use, but it will also handle the following two
cases correctly:

- a "0" as the first digit in the input will be shown correctly in the
output

- an input without any digits at all will give the empty string as
output (rather than 0).

Hope this helps / Lars-Åke

Excellent

"Lars-Ã…ke Aspelin" wrote:

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


Here is another formula you may try:

=MID(SUMPRODUCT(-
-MID("01"&A1,SMALL((ROW(A1:A300)-1)*ISNUMBER(-MID("01"&A1,ROW(A1:A300),1)),ROW(A1:A300))+1,1),10 ^(300-ROW(A1:A300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 15 digits in the input string.
(Following digits will be shown as zeroes.)

Maybe of no pratical use, but it will also handle the following two
cases correctly:

- a "0" as the first digit in the input will be shown correctly in the
output

- an input without any digits at all will give the empty string as
output (rather than 0).

Hope this helps / Lars-Ã…ke

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

Here is another formula you may try:

=MID(SUMPRODUCT(-
-MID("01"&A1,SMALL((ROW(A1:A300)-1)*ISNUMBER(-MID("01"&A1,ROW(A1:A300),1)),ROW(A1:A300))+1,1),10 ^(300-ROW(A1:A300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 15 digits in the input string.
(Following digits will be shown as zeroes.)

Maybe of no pratical use, but it will also handle the following two
cases correctly:

- a "0" as the first digit in the input will be shown correctly in the
output

- an input without any digits at all will give the empty string as
output (rather than 0).

Very nice! I have been working on a solution using approximately the same
approach, but not having any success (it looks like the difference is your
"01" concatenated on the front of A1... I'll have to think on that).

Here is your formula modified to allow it to be able to be copied down (all
I did is replace your A1:A300 references with $1:$300)...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10 ^(300-ROW($1:$300))),2,300)

By the way, I only show this formula working for 14 significant digits, not
15.

Rick

On Sat, 26 Jul 2008 10:48:52 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Here is another formula you may try:

=MID(SUMPRODUCT(-
-MID("01"&A1,SMALL((ROW(A1:A300)-1)*ISNUMBER(-MID("01"&A1,ROW(A1:A300),1)),ROW(A1:A300))+1,1),10 ^(300-ROW(A1:A300))),2,300)

This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER
rather than just ENTER.

It has the following (known) limitations:

- The input string in cell A1 must be shorter than 300 characters

- There must be at most 15 digits in the input string.
(Following digits will be shown as zeroes.)

Maybe of no pratical use, but it will also handle the following two
cases correctly:

- a "0" as the first digit in the input will be shown correctly in the
output

- an input without any digits at all will give the empty string as
output (rather than 0).

Very nice! I have been working on a solution using approximately the same
approach, but not having any success (it looks like the difference is your
"01" concatenated on the front of A1... I'll have to think on that).

Here is your formula modified to allow it to be able to be copied down (all
I did is replace your A1:A300 references with $1:$300)...

=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10 ^(300-ROW($1:$300))),2,300)

By the way, I only show this formula working for 14 significant digits, not
15.

Rick


Oh yes, the 15 was including the "1" that I added in the latest stage
to be able to handle 0 as the first digit.
So 14 digit is the maximum as you point out
Thanks for correcting the references to make the copyable.

Lars-Åke

Thanks Everybody

Excellent

Hardeep kanwar





Lars-Ã…ke Aspelin wrote:
On Sat, 26 Jul 2008 10:48:52 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,
UIEIROIWS12556ERE545,ADAS15455212AD4564AD2
[quoted text clipped - 45 lines]

Rick

Oh yes, the 15 was including the "1" that I added in the latest stage
to be able to handle 0 as the first digit.
So 14 digit is the maximum as you point out
Thanks for correcting the references to make the copyable.

Lars-Ã…ke

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