removing spaces between the numbers
 

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?

removing spaces between the numbers
 

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

removing spaces between the numbers
 

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

removing spaces between the numbers
 

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

removing spaces between the numbers
 

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?

removing spaces between the numbers
 

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
--

removing spaces between the numbers
 

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?

removing spaces between the numbers
 

.. 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).