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
==============================
