Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text and numbers in cell
I want to delete the text in 30.000 cells from a cell which contains text and
numbers. What is the easiest way to do so? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text and numbers in cell
Hi Henk
Copy this code and use the function Digitsonly(a2), copy down, copy and paste values hth regards from Brazil Marcelo ************************************************** *** Public Function DigitsOnly(sStr As String) As Variant Dim oRegExp As Object Set oRegExp = CreateObject("VBScript.RegExp") With oRegExp .IgnoreCase = True .Global = True oRegExp.Pattern = "\D" DigitsOnly = oRegExp.Replace(sStr, vbNullString) End With End Function ************************************************** **** "Henk" escreveu: I want to delete the text in 30.000 cells from a cell which contains text and numbers. What is the easiest way to do so? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
text and numbers in cell
Henk
Sub RemoveAlphas() ' Remove alpha characters from a string. ' except for decimal points and hyphens. 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 'change to [0-9] if dec. pt. and hyphen not required strNotNum = Mid(rngR.Value, intI, 1) Else: strNotNum = "" End If strTemp = strTemp & strNotNum Next intI rngR.Value = strTemp Next rngR End Sub Gord Dibben MS Excel MVP On Wed, 9 Aug 2006 06:43:02 -0700, Henk wrote: I want to delete the text in 30.000 cells from a cell which contains text and numbers. What is the easiest way to do so? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
Extract numbers from cell with Text and Numbers | New Users to Excel | |||
Using numbers as numbers in a cell having text | Excel Discussion (Misc queries) | |||
text and numbers same cell and formulas still work (like lotus) | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |