Remove 1000 separator space problem

  • Thread starter Thread starter joost
  • Start date Start date
J

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
 
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
Click to expand...

If the number stays under 6 digits then-

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

Seems to work

Matthew
 
Edit>Replace

What: hit spacebar once

With: nothing

Replace all


Gord Dibben MS Excel MVP
 
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
Click to expand...

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

=SUBSTITUTE(A1," ","")

Rick
 
Back
Top