Removing text from cells

[don wonnell]

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.

 

[Jacob Skaria]

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&"0123456789")),LEN(A1))


If this post helps click Yes

 

[Shane Devenshire]

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.

 

[Gord Dibben]

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