Using formula "VALUE" with pasted text

[Arnfinn Vartdal]

I use Excel 2003, Norwegian version. I try to convert a big table pasted
from a web site.
Pasted text is numbers in the format "7 589".
When I try to change a pasted text to a number using the formula "VERDI"
(Value in English), I get an error.
BUT - If I type in the number in the same format, it works.

The same thing happens when I paste the formula =VERDI("kr 1 000") from the
help system. Error.
If I type in exactly the same formula, it works.

Does anybody know why?
Is there something I can do?

Arnfinn

 

[Arvi Laanemets]

Hi

First way: use the formula in another column, like
=1*SUBSTITUTE(A2," ","")

Second way: (converts text to numbers in same column)
Format the range with your data in non-text format (p.e. General);
enter 1 or 0 into some empty cell, and copy it;
Select the range with your data, and then PasteSpecial.Multiply or
PasteSpecial.Add (depending the value you copied before). OK.
(When this doesn't work, then probably you have specific space characters in
text - you have to replace them with nothing {select the range, paste or
enter as ASCII code the character, and then ReplaceAll} before)

 

[Ron Rosenfeld]

I use Excel 2003, Norwegian version. I try to convert a big table pasted
from a web site.
Pasted text is numbers in the format "7 589".
When I try to change a pasted text to a number using the formula "VERDI"
(Value in English), I get an error.
BUT - If I type in the number in the same format, it works.

The same thing happens when I paste the formula =VERDI("kr 1 000") from the
help system. Error.
If I type in exactly the same formula, it works.

Does anybody know why?
Is there something I can do?

Arnfinn

Data imported from the web sometimes has both printing and non-printing spaces
included.

Usually, one can get rid of these by using a formula of the type:

=SUBSTITUTE(SUBSTITUTE(A1," ",""),CHAR(160),"")


--ron

 

[David McRitchie]

Hi Arnfinn ,
Minor revision on Ron's description, I think he meant
non-breaking-space (  in HTML) or required blank
(RBL in some languages) instead of non printing spaces.
In HTML additional spaces are not printed, but use of  
allows extra spaces to print -- it is named non-breaking because
a line will not break on a non-breaking space.

Rather than using a worksheet formula, my preference would be
to use a macro to change the values in place once and for all
to be done with it.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall