Excel changing syntax after using "text to columns" function (URGENT)

[ahmadw]

Hi,
I have a column which should contain numbers of same order of magnitude. The values you see that I have pasted in, is straight out of my Excel sheet, where the problem initiates. The first 7 values are good and look as expected. But then suddenly it goes to 10^7 instead of being in one-point-something/zero-point-something. If it wasn't because of the fact that it also switches to lower values sometimes (see "very bad") then I would be able to just multiply by 10^-7. But because of this, i cannot. I should mention manually fixing it is impossible. I have this problem in 8 sheets, with more than 1000 numbers in each. Also in three sheets with more than 7000 numbers. The values come from a datalogger at my university which I have been used to testing.
I hope you understand my problem and can help me solve it. I use both Office 2010 and 2013.

0.98214339 (good)
0.98495423 (good)
0.98770445 (good)
0.9905972 (good)
0.99338463 (good)
0.99616996 (good)
0.99907549 (good)
10.018.161
10.046.343
10.074.505
10.103.401
10.131.392
10.159.192
10.186.694
1.021.576 (VERY BAD)
10.243.581
10.271.509

Kind Regards
Ahmad

[nathansav]

10.018.161 10 18 161


Mine came through as numbers, can you attach the sheet?

[ben_hensel]

I'm going to guess your problem originates with the difference between how English-speaking notation uses a full stop (".") for the decimal place while commas (",") increment three orders magnitude... while many other European countries use the opposite notation. (I'm guessing this simply based on your location in Copenhagen).

Can you post an example dataset, with "before and after" cases for example? I can't think of a solution off-hand, so I'd like to dig into that.

[ahmadw]

Hi guys, thanks for your quick replies. I am going mentally unstable right now, since I do not have the data without converting the text into columns. The data in this one is with "." seperation at first. I dont mind that. I can easly convert it to ",".
https://www.dropbox.com/s/zgh7byyp1w...014_43_41.xlsx

And I also think it has something to do with the difference in notation between "," and "."

Edit: You should be aware the datalogger runs something like Office 2003.

[ben_hensel]

whataminute

did you post the after-the-fact stuff?

Can you post a slice of the data before it goes through text-to-columns?

If you're having trouble with text-to-columns, I'd like to see what the behavior of the converter is, rather than try to guess based on the ouput.

[ben_hensel]

You're talking about the range in column C, right?
"101 <Last> VDC"
is the row title?

That's stored as a text string, not a number.

(I see that you're getting the same behavior starting a few hundred rows down in the F column, "102 <Flytning> (VDC)", which might need handling too).

You don't actually need to use the text-to-column thing; in fact, that might be the wrong approach for your needs. The guesswork that text-to-columns uses for column divisors is probably screwing you up.

All you need to do is convert numbers stored as text to numbers.

If you don't mind using dummy columns (or inflating the size of your data somewhat needlessly, then you could just create a helper column that does this:

K13 = C13 +0

Simply adding zero forces the value in cell C13 to get assessed as a number, so the output in K13 will be whatever C13 is, but a number, not text.

Then you can copy/paste the values in column K over the top of column C stuff, and kill the helper column, to reduce the filesize back down.

[ahmadw]

Hmm,
Ben, it might just be me who does not understand what you are saying properly, or maybe you misunderstood my problem. But I can illustrate what I want.
0.98214339 (good)
0.98495423 (good)
0.98770445 (good)
0.9905972 (good)
0.99338463 (good)
0.99616996 (good)
0.99907549 (good)
10.018.161
10.046.343
10.074.505
10.103.401
10.131.392
10.159.192
10.186.694
1.021.576 (VERY BAD)
10.243.581
10.271.509


To turn into

0.98214339
0.98495423
0.98770445
0.9905972
0.99338463
0.99616996
0.99907549
1.0018161
1.0046343
1.0074505
1.0103401
1.0131392
1.0159192
1.0186694
1.021576 (notice this one had a number less, and that is why i cannot multiply by 10^7)
1.0243581
1.0271509


And you are right about the it is column C. Also column F has a problem that I became aware about a couple of minutes ago. But I dont understand what converting text to numbers can do for me now? I still stand with the same problem - that it changes magnitude to 10^7 and sometimes only 10^6.

[ben_hensel]

What I'm wondering is if the number range in column C is doing this because of something excel did, or if it's an error from further upstream, eg, coming out of the datalogger.

If it's upstream, then that means you're doing some data processing, which is totally fine, but not something you want to do by accident.

If it's something excel is doing, then I would prefer to try different processing so we're not introducing unnecessary noise with what should be lossless data handling.

Anyway try something like this in a helper column:

=IF(VALUE(C13)<10, VALUE(C13), C13 / 10^(LEN(ROUND(C13,0))-1))

This should output the number you want, as like an immediate solution to your woes. It will assume anything greater than 10 (you can make the IF term-test like 100 or 1,000 if you want) is screwed up and divide it by its own size-1 to be 1+change.

[ahmadw]

Ben Hensel,
Thank you very much for your help. That is exactly what I was looking for. I am not that sharp in Excel programming. I am more used to using Matlab and such. But I understand your code. I am just not unable to make one in Excel myself. Thank you very much. You have been upmost helpful. And I can do the exact same thing for column F and change the number 10 (if necessary).

Edit: But I have just one think to ask. Can you also make an expression to go the other way around?

For this given case:
-0,708
-0,754
-0,793
-0,837
-0,878
-0,919
-0,953
-0,99
-0,000104503 (bad, should be -1,04503)
-0,000107374
-0,000112264
-0,000118111
-0,000122044
-0,000126084

[ben_hensel]

Hm... I couldn't find a way to get the order-of-magnitude right from the length correctly, so I had to go with a logrithmic approach, lopping off stuff with truncations and absolute values:

=IF(ABS(C13) > 0.01, VALUE(C13), C13 * 10^(TRUNC(ABS(LOG(ABS(B2))), 0) +1))

So this normalizes off the absolute value using ABS, so it's any number that's less than 0.01 away from zero in either direction of the number line;

And then we tell it how big it's supposed to be by sticking it right between 0<|x|<1 and increasing by another 10x.

[ahmadw]

It is more than enough. Thank you very much for your help!