Alphanumeric

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

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

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

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

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