Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 ============================== |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
|
|||
|
|||
That worked perfectly. Thank you very much!
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting characters in a column of cells -converting to text strin | Excel Programming | |||
Macro For Deleting Cells Containing A Number | Excel Programming | |||
Finding & deleting cells that do not contain a certain text string | Excel Programming | |||
Moving, deleting, and concatenating text in cells. | Excel Worksheet Functions | |||
deleting certain text from cells | Excel Worksheet Functions |