How to convert text to number format

[Rasoul Khoshravan Azar]

I copy a table of numbers from web to Excel.
They appear as text and I can't do algebric command on them
(For example their "sum" become zero)
I know it is possible to change number format to text format but is there
any command to convert numbers to texts so I can sum up them?
Changing the format from:
Format/Cells Number tab and category box doesn't help.
Any comment is appreciated
TIA Rasoul

 

[Dave Peterson]

If that doesn't work, maybe you have those HTML non-breaking spaces in it.

You could use David McRitchie's Trimall macro that cleans things up.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

David also has instructions on how to install a macro and run it at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Rasoul Khoshravan Azar]

Although I couldn't understand the philosophy beyond your solution, I did it
but it doesn't ork. Still the results are texts and I can't add, subtract
them. Could you tell me what I am doing wrong?

 

[Peo Sjoblom]

Do this first, edit>replace, find what put the cursor in the box and hold
down Alt key while typing
0160 on the numeric keypad, release the Alt button. Leave the replace with
box empty. select replace all.
No format the range as General if it isn't already and follow Gord's
instructions.
Copying from the web many times resulting in trailing invisible html crap.
The above will replace them with null.
Then you should be able to force calculation with the add empty cell method
which adds zero to your numbers there by forcing Excel to see them as
numeric

 

[ryanb.]

Do this first, edit>replace, find what put the cursor in the box and hold
down Alt key while typing
0160 on the numeric keypad, release the Alt button. Leave the replace with
box empty. select replace all.
No format the range as General if it isn't already and follow Gord's
instructions.
Copying from the web many times resulting in trailing invisible html crap.
The above will replace them with null.
Then you should be able to force calculation with the add empty cell method
which adds zero to your numbers there by forcing Excel to see them as
numeric

--

Regards,

Peo Sjoblom

Does =VALUE() not work with text pasted from websites? Our ERP system spits
out a bunch of trailing spaces following numbers, but =VALUE() has always
worked to take care of those.

just curious,

ryanb.

 

[Peo Sjoblom]

Does =VALUE() not work with text pasted from websites? Our ERP system
spits

out a bunch of trailing spaces following numbers, but =VALUE() has always
worked to take care of those.

just curious,

Not if they are char(160) characters
You can try it yourself
type

=123456&CHAR(160)

in let's say A1, copy and paste special as values in the same cell

=VALUE(A1)

will return #VALUE!

now follow my instructions with the edit>replace stuff and see what's
happen,
Personally I use a macro by David McRitchie

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


which will take care of all craps

 

[2rrs]

Select a column, Data->Text to Columns...->Finish.

See if this helps.