question about removing text from cells, leaving numbers
 

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.


--
JPN5804
------------------------------------------------------------------------
JPN5804's Profile: http://www.excelforum.com/member.php...o&userid=29137
View this thread: http://www.excelforum.com/showthread...hreadid=488569

question about removing text from cells, leaving numbers
 

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

"JPN5804" wrote in
message ...

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.


--
JPN5804
------------------------------------------------------------------------
JPN5804's Profile:
http://www.excelforum.com/member.php...o&userid=29137
View this thread: http://www.excelforum.com/showthread...hreadid=488569

question about removing text from cells, leaving numbers
 

Hello again, and thanks. Yes, commas separate the text, so I'll try
that.

gracias!


--
JPN5804
------------------------------------------------------------------------
JPN5804's Profile: http://www.excelforum.com/member.php...o&userid=29137
View this thread: http://www.excelforum.com/showthread...hreadid=488569

question about removing text from cells, leaving numbers
 

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.