Macro OK with you?

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

After losing all the text, do a text to columnsfixed width to split out to
seperate cells.


Gord Dibben Excel MVP

On Sun, 27 Nov 2005 23:32:36 -0600, JPN5804
wrote:


This forum has already proved helpful, I've already gotten help for a
problem.

I've got another problem, however. I've got a large number of cells
with both numbers and text, I need to extract the text, leaving the
numbers so I can add them. The problem is, the number of characters
varies. Here are some examples of cells:

"3 I am so sad or unhappy that I can't stand it., 2 I am sad all the
time and I can't snap out of it., 1 I feel sad."

I need to remove the text, leaving the 3 and the two, then I could do
text to columns to get the 3 and 2 into their own cells. The problem
is, I the number of characters varies, for example, another cell
contains "3 I am very sad., 2 I am somewhat sad."

Thanks again.