Deleting text from number cells
 

Does anyone know of a quick and easy way to remove text characters from within a number? My numbers look like 186R1114F0010, 186011T3F0233 and 18H01135F0557. I have about 3,000 cells to convert to an all numeric number per day. Currently I am using the find/replace function on every letter in the alphabet. This works well but is a little tedious.

thank you for any help you can give

Deleting text from number cells
 

On Tue, 15 May 2012 22:41:13 +0000, mytyab wrote:


Does anyone know of a quick and easy way to remove text characters from
within a number? My numbers look like 186R1114F0010, 186011T3F0233 and
18H01135F0557. I have about 3,000 cells to convert to an all numeric
number per day. Currently I am using the find/replace function on every
letter in the alphabet. This works well but is a little tedious.

thank you for any help you can give

Easily done with a macro. The following assumes your data to be manipulated is in Column A, and that all the contents of column A need to have non-digits removed. If that is not the case, a different cell selection routine can be developed easily, if you like this approach. If the routine runs too slowly, it can be sped up.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), ensure the sheet with your data is active. (Work on a copy until you are happy with the results.
<alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

======================================
Option Explicit
Sub RemText()
Dim rSrc As Range, c As Range
Dim re As Object
Const sPat As String = "\D*"
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
re.Global = True

Set rSrc = Range("A1", Cells(Rows.Count, "A").End(xlUp))

Application.ScreenUpdating = False
For Each c In rSrc
c.Value = re.Replace(c.Text, "")
Next c
Application.ScreenUpdating = True
End Sub
==============================

Deleting text from number cells
 

Add this UDF to a general module in your workbook.

Function RemAlpha(str As String) As String
'Remove Alphas from a string
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\D"
RemAlpha = re.Replace(str, "")
End Function

usage is =RemAlpha(cell)

Copy down as far as you need.


Gord

On Tue, 15 May 2012 22:41:13 +0000, mytyab
wrote:


Does anyone know of a quick and easy way to remove text characters from
within a number? My numbers look like 186R1114F0010, 186011T3F0233 and
18H01135F0557. I have about 3,000 cells to convert to an all numeric
number per day. Currently I am using the find/replace function on every
letter in the alphabet. This works well but is a little tedious.

thank you for any help you can give

That worked perfectly. Thank you very much!