how to convert numerical data stored as text into number in excel

S

suhit

i downloaded numerical data from a website by copying and pasting it into
excel. but it got stored as text. in order to perform calculations i need to
convert it into number. i tried formatting the cells by right clicking and
selecting format as number. but it didn't work. can anyone plz help? thanks!
 
L

Lars-Erik

Hi,
Save your downloaded file as xxx.txt and reopen as a new file, then you get
a lot of possible choise's.
Lars-Erik

"suhit" skrev:
 
G

Gary''s Student

As long as the cells that you want to numerify don't contain extra junk (like
spaces), try to fix them as follows:

1. in an un-used cell enter 1
2. copy the cell
3. select the cell(s) you want to convert and
4. Edit > Paste Special > check the multiply button
 
S

suhit

hey thanx guys...but the problem is that the cells do contain a space at the
end...what shud i do....
 
G

Gary''s Student

If you are willing to use VBA, then enter this macro:

Sub numerify()
Dim r As Range
Count = 0
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
MsgBox (Count & " cells changed")
End Sub

Then run it.
 
S

suhit

Hey Gary's Student,

Thanx Buddy. u made my Job much easier. Really grateful to you buddy. Thanx
Man!! God Bless You.
 
Top