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

Check your other post.

Syed Rizvi wrote:

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

--

Dave Peterson

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

--
Gary''s Student - gsnu200778


"Syed Rizvi" wrote:

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

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200778


"Syed Rizvi" wrote:

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

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/Writin...ionsInVBA.aspx

--
Gary''s Student - gsnu200778


"Syed Rizvi" wrote:

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200778


"Syed Rizvi" wrote:

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

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&"0123456 789")),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


"Syed Rizvi" wrote in message
...
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

Thanks very much folks, much appreciated

"Rick Rothstein (MVP - VB)" wrote:

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&"0123456 789")),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


"Syed Rizvi" wrote in message
...
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