+ Reply to Thread
Results 1 to 11 of 11

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

Hybrid View

  1. #1
    Registered User
    Join Date
    07-02-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Office 2010 & 2013
    Posts
    5

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

    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
    Last edited by ahmadw; 07-03-2014 at 06:35 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

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

    10.018.161 10 18 161


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

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

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

    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.

  4. #4
    Registered User
    Join Date
    07-02-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Office 2010 & 2013
    Posts
    5

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

    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.

  5. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

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

    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.

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

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

    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.

  7. #7
    Registered User
    Join Date
    07-02-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Office 2010 & 2013
    Posts
    5

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

    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.

  8. #8
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

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

    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.

  9. #9
    Registered User
    Join Date
    07-02-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Office 2010 & 2013
    Posts
    5

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

    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
    Last edited by ahmadw; 07-02-2014 at 12:11 PM.

  10. #10
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

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

    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.

  11. #11
    Registered User
    Join Date
    07-02-2014
    Location
    Copenhagen, Denmark
    MS-Off Ver
    Office 2010 & 2013
    Posts
    5

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to stop "today" function from changing the date every day in excel
    By Haneen H in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-21-2013, 12:25 PM
  2. [SOLVED] Using "SOLVER" to match two columns, but how to copy it for multiple rows ??? (URGENT)
    By hshahad in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 11:31 AM
  3. [SOLVED] How to check two columns for the presence of text and returning "yes" or "no"
    By Karelia Suite in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-19-2013, 09:36 AM
  4. Replies: 9
    Last Post: 09-05-2012, 10:23 AM
  5. Question about "text to columns" function
    By anna57 in forum Excel General
    Replies: 4
    Last Post: 02-02-2010, 01:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1