Remove 1000 separator space problem

[joost]

I am importing through a query numbers which the 1000 separator is a space.
To calculate with this value isn't possible in excel because the 1000
separator is this space.

I am try to remove the space in excel with find and replace " " to "" but
this isn't possible.
Also with VBA isn't this possibel and even not with format cells isn't this
possibel.

Who can help me. A sample of the data you can find below.

Thanks in advance.


9 365
10 598
9 087
1 397
8 903
8 136
5 524
19 584
9 329
14 896
14 686
8 198
10 500
3 453

 

[Matthew]

I am importing through a query numbers which the 1000 separator is a space.
To calculate with this value isn't possible in excel because the 1000
separator is this space.

I am try to remove the space in excel with find and replace " " to "" but
this isn't possible.
Also with VBA isn't this possibel and even not with format cells isn't this
possibel.

Who can help me. A sample of the data you can find below.

Thanks in advance.

9 365
10 598
9 087
1 397
8 903
8 136
5 524
19 584
9 329
14 896
14 686
8 198
10 500
3 453

If the number stays under 6 digits then-

=VALUE(TRIM(LEFT(A3,(FIND(" ",A3)-1))&RIGHT(A3,3)))

Seems to work

Matthew

 

[Gord Dibben]

Edit>Replace

What: hit spacebar once

With: nothing

Replace all


Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

I am importing through a query numbers which the 1000 separator is a

If the number stays under 6 digits then-

=VALUE(TRIM(LEFT(A3,(FIND(" ",A3)-1))&RIGHT(A3,3)))

Seems to work

You could also do this instead (no limit on the size of the value this
way)...

=SUBSTITUTE(A1," ","")

Rick