Removing number spacing in cells

[Mark]

Basically I am making a calculator that will parse number changes. In order
to do the math operations correctly I must convert my initial input to
standard number format first.

4 500 ---> 4500
23 345 --> 23345

I would like to do this with a new cell that calls upon the value in the old
and converts it.

So for example in A1 I have 4 500
and in B1 I want to have 4500 without manually removing the space.

I've looked online and found the val function and tried it but it does not
work for some reason. I tried to enable VBA but it seems it still does not
work.

I try Val(A1) and the cell gives me a #NAME? error.

Does anybody have any ideas of a differant function I can use to remove the
spacing or a way to get the Val function working?

 

[Mike]

=VALUE(SUBSTITUTE(A1," ",""))
=VAL(SUBSTITUTE(A1," ",""))

 

[Joel]

=val(SUBSTITUTE(A1," ",""))

 

[Rick Rothstein]

There is no worksheet VAL function, but there is a VALUE function (as Mike
showed in his post); however, this will save a few characters over using it
(and it avoids the function as well)...

=--SUBSTITUTE(A1," ","")

 

[Max]

"Add a zero" or "Multiply by 1" would be alternative ways
to coerce the resulting text nums returned by SUBSTITUTE
to real numbers w/o impacting intrinsic values, eg:
=SUBSTITUTE(A1," ","")+0
=SUBSTITUTE(A1," ","")*1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik