Removing Text from Alphanumeric values

S

Syed Rizvi

Hi
I have a column containing alphanumeric values e.g.
HG06100
H134679
HC134679
HG05534
A434340
I need a formula or macro help to remove all the text values in the cell and
leave all the numbers behind including zero. Could someone please help, thanks

Syed
 
D

Dave Peterson

Check your other post.

Syed said:
Hi
I have a column containing alphanumeric values e.g.
HG06100
H134679
HC134679
HG05534
A434340
I need a formula or macro help to remove all the text values in the cell and
leave all the numbers behind including zero. Could someone please help, thanks

Syed
Click to expand...
 
G

Gary''s Student

Try this UDF:

Function numit(r As Range) As Double
Dim s As String, s2 As String, c As String
s2 = ""
s = r.Value
l = Len(s)
For i = 1 To l
c = Mid(s, i, 1)
If c Like "#" Then
s2 = s2 & c
End If
Next
numit = --s2
End Function
 
R

Rick Rothstein \(MVP - VB\)

If, as all your examples show, the non-digits are always in front of the
digits, you can use this formula...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)

The 255 in the above formula assumes your values are never longer than 255
characters; you can lower this number to a value equal to or greater than
the length of the longest alphanumeric value you will ever process.

Rick
 
S

Syed Rizvi

I am not familiar how to define this Excel, could you please give me some
more instructions how to use this code please.
many thanks

Syed
 
S

Syed Rizvi

Thanks very much folks, much appreciated

Rick Rothstein (MVP - VB) said:
If, as all your examples show, the non-digits are always in front of the
digits, you can use this formula...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),255)

The 255 in the above formula assumes your values are never longer than 255
characters; you can lower this number to a value equal to or greater than
the length of the longest alphanumeric value you will ever process.

Rick
Click to expand...
 
G

Gary''s Student

A UDF is a way to create your own function and use it in the worksheet just
like SUM, AVERAGE, MIN, MAX, etc. UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel function:

=numit(A1)

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
 
Top