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 one of your other posts.


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

You might use this from Ron Rosenfeld and others. It will remove any text in
any position.

Sub RemoveAlpha()

Dim tbl As Range, rng As Range

With Sheets(1)
.Columns("A").NumberFormat = "@"
Set tbl = .Range("A1:A5")
End With

For Each rng In tbl
GetNumSlash rng
Next

End Sub

Public Function GetNumSlash(rng As Range) As String

Dim i As Long, sStr1 As String, sChar As String

For i = 1 To Len(rng)
sChar = Mid(rng, i, 1)
If Not sChar Like "*[!0-9]*" Then sStr1 = sStr1 & sChar
Next

rng = sStr1

End Function

Geoff

"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

Thanks very much folks, much appreciated

"Geoff" wrote:

You might use this from Ron Rosenfeld and others. It will remove any text in
any position.

Sub RemoveAlpha()

Dim tbl As Range, rng As Range

With Sheets(1)
.Columns("A").NumberFormat = "@"
Set tbl = .Range("A1:A5")
End With

For Each rng In tbl
GetNumSlash rng
Next

End Sub

Public Function GetNumSlash(rng As Range) As String

Dim i As Long, sStr1 As String, sChar As String

For i = 1 To Len(rng)
sChar = Mid(rng, i, 1)
If Not sChar Like "*[!0-9]*" Then sStr1 = sStr1 & sChar
Next

rng = sStr1

End Function

Geoff

"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