Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hungarian excel problem with thousand separator
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hungarian excel problem with thousand separator
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. -- David Biddulph "Bige" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hungarian excel problem with thousand separator
On 20 Ott, 11:00, "David Biddulph" <groups [at] biddulph.org.uk
wrote: 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. -- David Biddulph "Bige" wrote in message ... 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 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hungarian excel problem with thousand separator
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 "Bige" wrote in message ... On 20 Ott, 11:00, "David Biddulph" <groups [at] biddulph.org.uk wrote: 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. -- David Biddulph "Bige" wrote in message ... 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 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hungarian excel problem with thousand separator
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-space234 and get it recognized as (the number) 1234, BUT you can paste 1<space234, 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-space234 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. "David Biddulph" wrote: 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 "Bige" wrote in message ... On 20 Ott, 11:00, "David Biddulph" <groups [at] biddulph.org.uk wrote: 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. -- David Biddulph "Bige" wrote in message ... 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 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge - Thousand separator and decimals | Excel Discussion (Misc queries) | |||
Excel 2002 : How to set thousand separator at tool bar ? | Excel Discussion (Misc queries) | |||
thousand separator | Excel Discussion (Misc queries) | |||
thousand separator conversion from dot to comma | Excel Discussion (Misc queries) | |||
thousand separator | Setting up and Configuration of Excel |