removing spaces between the numbers

M

martin

Hello all!

When copying number from Navision account program to Excel, it copies
numbers with spaces as text, but not as numerical values.
Such value: 1 109 014,08 is copied as text.

One possibility to set the number actually to a numerical value is to delete
the spaces within the number, so after doing that it looks as 1109014,08.
Then i can add, multiply, divide this number with ease.

Is there a automatic possibility how remove those spaces?
Is there an alterantive?
 
P

Pete

Use Search and Replace - highlight all the cells, then Edit | Replace
(or CTRL-H). In the Find box type a single space, leave the Replace
With box with nothing in it and then click Replace All.

Pete
 
M

martin

P.S. your solution for some reason is not working : excel cannot find the
data you specified...
 
M

Max

Is there a possibility for a formula solution?

Assuming source data in A1 down
Try in say, B1: =SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")+0
Copy down
 
M

Max

Is there a possibility for a formula solution?

Assuming source data in A1 down
Try in say, B1: =SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")+0
Copy down
 
M

Max

.. We are getting closer:)

Copied the "blank" from the source data in A1 and then pasted it inside the
suggested formula to replace the earlier space: " ", and it seems to work ok
<g>

Here's the sample file with the formulas implemented in col B
http://cjoint.com/?molmOXqRDy
martin_problem_numbers.xls

Col C contains just a slight revision to the formula in col B (added
error-trap):
=IF(A1="","",SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")+0)
 
Top