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?

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

Is there a possibility for a formula solution?

Thanks!
Martin

"Pete" wrote:

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

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

"Pete" wrote:

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

Is there a possibility for a formula solution?

Assuming source data in A1 down
Try in say, B1: =SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")+0
Copy down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"martin" wrote in message
...
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?

Is there a possibility for a formula solution?

Assuming source data in A1 down
Try in say, B1: =SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")+0
Copy down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

We are getting closer:)

It cames out that the format is not even text!

You can find the xls file at:
http://www.zone.ee/virka/problem_numbers.xls (click on "Edasi
Koduleheküljele" button in the centre).

Try with this.

"Max" wrote:

Is there a possibility for a formula solution?

Assuming source data in A1 down
Try in say, B1: =SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")+0
Copy down
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"martin" wrote in message
...
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?

.. 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)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"martin" wrote in message
...
We are getting closer:)

It cames out that the format is not even text!

You can find the xls file at:
http://www.zone.ee/virka/problem_numbers.xls (click on "Edasi
Koduleheküljele" button in the centre).