Remove 1000 separator space problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

joost - 01 Dec 2007 18:43 GMT

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

Reply to this Message

Matthew - 01 Dec 2007 18:52 GMT

> 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
[quoted text clipped - 23 lines]
> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 01 Dec 2007 19:40 GMT

>> I am importing through a query numbers which the 1000 separator is a
>> space.
[quoted text clipped - 14 lines]
>
> Seems to work

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

=SUBSTITUTE(A1," ","")

Rick

Reply to this Message

Gord Dibben - 01 Dec 2007 18:55 GMT

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 space.
>To calculate with this value isn't possible in excel because the 1000
[quoted text clipped - 23 lines]
> 10 500
> 3 453

Reply to this Message

Rate this thread: