Suppose Cell A1 has "TWID10293 Comments" entry an
cell A2 has "TW298382 Comment on this." entry
I wish to retrieve the numeric part of A1 and A2 in B1 and B2 respectively. ie B1 should have "10293" and B2 should have "29382".

If you wish to retain the original data, copy Column A to B then run this
macro on Column B.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub

Gord Dibben Excel MVP

On Tue, 24 Feb 2004 21:56:05 -0800, Excel Prg
wrote:

Suppose Cell A1 has "TWID10293 Comments" entry and
cell A2 has "TW298382 Comment on this." entry.
I wish to retrieve the numeric part of A1 and A2 in B1 and B2 respectively. ie B1 should have "10293" and B2 should have "29382".

See the 'Val' function in VBE help.

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

Suppose Cell A1 has "TWID10293 Comments" entry and
cell A2 has "TW298382 Comment on this." entry.
I wish to retrieve the numeric part of A1 and A2 in B1 and B2 respectively. ie B1 should have "10293" and B2 should have "29382".