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

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

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

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

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.