question about removing text from cells, leaving numbers

  • Thread starter Thread starter JPN5804
  • Start date Start date
J

JPN5804

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.
 
Hi!

It looks like the phrases are separated by commas.

Do Text to Columns delimited by comma.

Then you can use this formula to extract the numbers:

=LEFT(TRIM(A1))*1

Biff
 
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 columns>fixed width to split out to
seperate cells.


Gord Dibben Excel MVP
 
Back
Top