Alphanumeric

[hardeep via OfficeKB.com]

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

 

[Bob Phillips]

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)

 

[Mike H]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

Thank In Advance

Hardeep

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

 

[hardeep via OfficeKB.com]

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

Thank In Advance

Hardeep

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

 

[Mike H]

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

 

[Bob Phillips]

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)

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

Thank In Advance

Hardeep

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

 

[Ron Rosenfeld]

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

 

[hardeep via OfficeKB.com]

Thanks Sir

Hardeep kanwar

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

 

[hardeep via OfficeKB.com]

Thanks Sir

Hardeep kanwar

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]

 

[Teethless mama]

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

....then use this formula

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

 

[Lars-Åke Aspelin]

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

 

[Mike H]

Excellent

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

 

[Rick Rothstein \(MVP - VB\)]

I have list of 15000 of Alphanumeric data for ex. ADEDO125ADSD589ADF121,

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

 

[Lars-Åke Aspelin]

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

 

[Ron Rosenfeld]

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

...then use this formula

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

Simpler and more inclusive:

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

 

[hardeep via OfficeKB.com]

Thanks Everybody

Excellent

Hardeep kanwar

[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

 

[beejay]

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

 

[T. Valko]

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.