Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing text from cells
Have Excel 2002, want to remove text from cells and leave the numerical data
in those cells intact. Don't see a method for this in worksheet functions listed by category / text & data. -- labor market analyst, NW Ohio |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing text from cells
Hi,
We need some sample strings to extract the numbers from. The solution can be very different depending on how the numbers and text are mixed up. Mike "don wonnell" wrote: Have Excel 2002, want to remove text from cells and leave the numerical data in those cells intact. Don't see a method for this in worksheet functions listed by category / text & data. -- labor market analyst, NW Ohio |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing text from cells
The below formula will extract contents from the 1st numeric onwards..Try
and post back whether this will work for all your data? spray 2.5oz -------------will return 2.5oz body spray 2.5oz -------will return 2.5oz =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),LEN(A1)) If this post helps click Yes --------------- Jacob Skaria "don wonnell" wrote: Have Excel 2002, want to remove text from cells and leave the numerical data in those cells intact. Don't see a method for this in worksheet functions listed by category / text & data. -- labor market analyst, NW Ohio |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing text from cells
Hi,
One way to interpret this questions, since you didn't give us any examples, is if cells contain text you want to clear them, if not you want them unchanged. 1. Press F5, Special, turn on Constants, and uncheck everything except Text, click OK. 2. Press the Del key. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "don wonnell" wrote: Have Excel 2002, want to remove text from cells and leave the numerical data in those cells intact. Don't see a method for this in worksheet functions listed by category / text & data. -- labor market analyst, NW Ohio |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing text from cells
Remove all but numbers using UDF
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 Macro to do in place. 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 MS Excel MVP On Mon, 8 Jun 2009 05:40:01 -0700, don wonnell wrote: Have Excel 2002, want to remove text from cells and leave the numerical data in those cells intact. Don't see a method for this in worksheet functions listed by category / text & data. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Text | Excel Discussion (Misc queries) | |||
Removing text from a cell with text and numbers | Excel Discussion (Misc queries) | |||
Removing text | Excel Discussion (Misc queries) | |||
question about removing text from cells, leaving numbers | Excel Discussion (Misc queries) |