Hungarian excel problem with thousand separator

[Bige]

Hello,

dear friends I have a problem with hungarian excel ... I would need to
import a text file with numbers, here it is (copy/paste from Notepad):

Part ID Description WH UM Type WH Location MyData MyValue
TEST1 test part first test part second 100 FG
4 C5D2 190,000 72,627 13 799,22
TEST2 RED ADHESIVE LABELS "4 Nm max" (for rope 100 FG 4 C5D2 250,000
91,160 22 790,04
TEST3 BL S4Z slow and S3R/L C-MOD SMALL cont p 100 NR 4 C5D0 1 400,000
760,402 1 064 563,49

What happens is that when I import it through Excel the number 1
400,000 becomes text even if all my setups are in hungarian and the
separator of thousands is a space. Guess that space is read by Excel
as not the correct space...

Could you please help me?

Thank you,
Ivan Pololi
Italy

 

[David Biddulph]

If you are saying that your data is formatted in such a way that you can
distinguish between field delimiters and your thousand separator, and hence
that your
1 400,000
is being read into one field, if it isn't being treated as a number it
sounds as if you don't have the correct Windows Regional Options set. These
are set up in Control Panel, not in Excel.

 

[Bige]

If you are saying that your data is formatted in such a way that you can
distinguish between field delimiters and your thousand separator, and hence
that your
 1 400,000
is being read into one field, if it isn't being treated as a number it
sounds as if you don't have the correct Windows Regional Options set.  These
are set up in Control Panel, not in Excel.

Hello David,

thank you for your reply but my settings are correctly set, I checked
again to be sure. : - ( That sounds to me as an Excel problem ...

Regards,
Ivan

 

[David Biddulph]

Have you checked whether the character used as a thousands separator is
actually a space?
If you have 1 400,000 in cell A1, what does =CODE(MID(A1,2,1)) show?
If it shows 32, you have a space; if you have 160, it is a non-breaking
space.
Are you sure that the character in use in your file is the same as the
character specified in your Windows Regional settings?
--
David Biddulph

If you are saying that your data is formatted in such a way that you can
distinguish between field delimiters and your thousand separator, and
hence
that your
1 400,000
is being read into one field, if it isn't being treated as a number it
sounds as if you don't have the correct Windows Regional Options set.
These
are set up in Control Panel, not in Excel.

Hello David,

thank you for your reply but my settings are correctly set, I checked
again to be sure. : - ( That sounds to me as an Excel problem ...

Regards,
Ivan

 

[Martin Hellspong]

At least in a Swedish version of Excel 2003 SP3 (on Swedish Win XP Pro SP3),
there seems to be the same problem, when the thousands separator is set to a
non-breaking space (character #160, entered by alt+numeric keypad 0160)
either in Windows Regional Settings or inside Excel. The non-breaking space
is the Swedish system default thousands separator in Windows.

If the thousands separator is set to non-breaking space, you cannot paste
1<non-breaking-space>234 and get it recognized as (the number) 1234, BUT you
can paste 1<space>234, and that will get recognized as a number, even though
the thousands separator is currently not space (character #32).

If the thousands separator IS set to the space character, you get the SAME
behavior; So space is recognized, and non-breaking space is not recognized as
the thousands separator. This is regardless if the change is made inside
Excel or in the Windows Regional Settings. Overriding the thousands separator
in Excel, closing/reopening the settings window, and pasting the configured
thousands separator into another application shows it is indeed the
non-breaking space.

Experimenting, several other values of the thousands separator, such as a
"Z", will get recognized by Excel, so pasting 1Z234 will get recognized as
(the number) 1234 when the thousands separator is set to "Z".

So it seems that the behaviour is broken somehow for the case of having the
non-breaking space as thousands separator, which is confused by Excel to mean
an ordinary space.

I could not find a setting which makes Excel recognize the non-breaking
space as thousands separator.

Also, copying a number formatted with thousands from Excel, gets pasted into
another application as a space, regardless if you set the thousands separator
to the non-breaking space or the ordinary space.

Pasting 1<non-breaking-space>234 into Excel, and then copying it into
another application, also preserves the non-breaking space.

The formula =VALUE(SUBSTITUTE(A1,CHAR(160),"")) will remove the non-breaking
spaces from the "text" in cell A1 and convert it into a number.