Excel gives me weird 5-digit numbers.

**Test123Test** · 04-26-2013, 10:17 AM

I'm having a weird problem. Numbers that are supposed to show up as "X.XX" for instance "1.05" from my DDE feed instead show up as some weird 5-digit numbers, like so "XXXXX.XX", for example "27395.00".
See attached picture for how it looks like.

I suspect it has to do with formatting, I tried to Format the cell and set it to "General", "numbers" and pretty much every option there is but it still shows up wrongly.

**Fermented Reptile** · 04-26-2013, 10:30 AM

Hi,

Where you have "27395.00" what are you expecting to see? If they're bigger numbers I'd posit it's a calculation problem rather than a formatting one...

**Test123Test** · 04-26-2013, 10:39 AM

The number is directly from a DDE-stream, and is supposed to show the bid and ask of a stock. I'm expecting to see the actual bid and ask of the stock, for instance for GOOGLE it would be 803.00 or something. Instead I see these 5-digit numbers that are way too high. Google certainly isn't valued at "27395.00"...

NOTE: "27395" is just an example. After analyzing one number in spesific it seems that for instance 12785.00 represents 1.35 which is the correct ask-price. 41548.00 represents 1.10. Not seeing any logical patterns here.

**MrShorty** · 04-26-2013, 10:52 AM

Out of curiosity, I took 12785 and formatted as date and got january 1 1935, so there seems to be a "1-35" in this number. 41548 formatted as date yields October 1 2013 (I could back out 1-10 from this as well). I'm wondering if something in the data stream is making Excel treat these numbers as dates. Have you got a way of looking at the data stream directly (in a text editor or similar) so you can see the actual data being fed to Excel?

**Test123Test** · 04-26-2013, 11:00 AM

Unfortunately no, but if you say the numbers can be backed out then probably it's an Excel problem, treating the numbers as dates.

Here are some more examples: 10990.00 = 2.3
1.15 = 42005

Since you are able to extract the numbers what is the formula you are using for this? I could just apply it and it would at least be a workaround.

**Test123Test** · 04-26-2013, 11:08 AM

I found the problem. My system separator is set to comma (,) for some reason. Setting it to period (.) instead made it work properly. No idea why that was a problem, but it's solved now.

**MrShorty** · 04-26-2013, 11:12 AM

I'm not using a formula. I'm simply formatting the number as a date (something like mm/dd/yyyy) http://office.microsoft.com/en-us/ex...433.aspx?CTT=1 See this for a quick description of how Excel interprets numbers as dates. http://office.microsoft.com/en-us/ex...674.aspx?CTT=1 And I'm still not seeing a good pattern.

Some ways of importing data into Excel (I'm most familiar with text data, so I know this is true for text data) allows you to specify the data type of column. Could you edit your data import so that this column comes in as text rather than general so that Excel will not try to convert it to a date?

On edit: glad you figure it out. One can chase that kind of problem around all day and not find it.