Alphanumeric

  • Thread starter hardeep via OfficeKB.com
  • Start date
H

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
 
B

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

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
 
R

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
 
H

hardeep via OfficeKB.com

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

Thank In Advance

Hardeep

Bob said:
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
 
H

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
 
M

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
 
B

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)

hardeep via OfficeKB.com said:
I want Numeric characters for example: 125589121,12556545,1545521245642 and
so on.

Thank In Advance

Hardeep

Bob said:
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
 
R

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
 
H

hardeep via OfficeKB.com

Thanks Sir

Hardeep kanwar

Ron said:
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
 
H

hardeep via OfficeKB.com

Thanks Sir

Hardeep kanwar

Mike said:
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
[quoted text clipped - 33 lines]
 
L

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
 
M

Mike H

Excellent

Lars-Ã…ke Aspelin said:
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
 
R

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
 
L

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
 
H

hardeep via OfficeKB.com

Thanks Everybody

Excellent

Hardeep kanwar





Lars-Ã…ke Aspelin said:
[quoted text clipped - 45 lines]

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
 
B

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top