converting European figures ( comma as thousand seperator) from .txt files into Excel

Posted by Gavin on February 16, 2001 2:28 AM

Does anyone know how to convert the following when importing into Excel:

European Text files are producing figures like 2.3, 3.45, 5.436 where the "." is the thousand seperator, so the actual numbers are 2300, 3450 and 5436 respectively.

The issue is that the file contains number of less than 999 so I cant just multiple the whole column by 1000.

(The report contains only whole number), so UK/US decimal places not occur.

Can anyone help me please

Thanks

Gavin

Posted by Mark W. on February 16, 2001 3:08 PM

Gavin, here's the "brute force" approach:

=IF(A1&LT;999,A1*1000,SUBSTITUTE(A1,".",",")+0)

Given time, I'm certain that someone can come up
with a more elegant method.

Posted by Mark W. on February 16, 2001 3:28 PM

Damn! My previous suggestion won't work...

My previous suggestion won't work. The formula
won't distinguish between a 1 and 1.000 (1,000).
So, let's consider our options. Are you importing
a .txt file? If so, are you using the Text to
Column wizard? If so, instead of using a General
format for the column at Step 3 of 3 why don't you
apply a Text format? This will prevent trailing
zeroes from being lost on import. Your values
would look like:

2.300
3.450
5.436
999

You could then do the following:

1. Using the Edit Replace menu command you can
replace occurrences of "." with ",".
2. Then you can convert the values that are less
than 1000 by multiplying them by 1 using Paste
Special... Values Multiply.

This approach will work if your values don't have
decimal places. Is that the case?

Posted by Aladin Akyurek on February 17, 2001 1:09 AM

Re: Damn! My previous suggestion won't work...

Following the first part of Mark's suggestion (see above), the formula

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".","#"),",","."),"#",",")

appears to work.

Note. Open from Excel the target .txt file, at Step 3 of Text Import Wizard choose for Text. This is, I reckon, what Mark is suggesting in the above passage.

Aladin

Posted by Aladin Akyurek on February 17, 2001 1:30 AM

Re: Damn! My previous suggestion won't work...

: won't distinguish between a 1 and 1.000 (1,000). : So, let's consider our options. Are you importing : a .txt file? If so, are you using the Text to : Column wizard? If so, instead of using a General : format for the column at Step 3 of 3 why don't you : apply a Text format? This will prevent trailing : zeroes from being lost on import. Your values : would look like: : 3.450 : 5.436 : 999

Wrap that formula with VALUE:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".","#"),",","."),"#",","))

Posted by Gavin on February 19, 2001 10:20 AM

Re: Damn! My previous suggestion won't work...

Guys,

Thanks for the work you have done for this little issue of mine, especially on a Saturday!, Im back in the office tomorrow (Tuesday) and I'll let you know how I get on.
Thanks once again,
Gavin