As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/

CURSE YOU EXCEL AND HOW YOU HANDLE TEXT/NUMBER FIELDS

Ok so i'm trying to reconcile a mapping file given by my corporate overlords to our chart of accounts. Unfortunately, the full string they give us is broken up by periods (like so 515.73120.62200.1825.31). we have to take the periods out to get it to fit in the user defined field we use. seems pretty straight forward right? i pulled a list of all our accounts and the mapping we have into excel, and i'm trying to compare via VLookup to the mapping from corporate. i get the delimited string from the corporate sheet, copy that and paste special/values into a Text column, and then find and replace all "." characters with blank. Excel seems to like to round what it thinks is a number now, and display in scientific notation.
so from 515.73120.62200.1825.31
I get 5157312062200180000
when i want 5157312062200182531

I tried popping an A in front, and that keeps the exact number when i remove the "." characters, but when i find/replace the "A", i get the same result. However, if i go into the cell itself, and manually delete the A, it's fine, and i get the green "want to change this into a number" flag on the cell. but that's not feasible b/c i have 3,000+ rows.

Anyone have any ideas?

Posts

  • bowenbowen How you doin'? Registered User regular
    Yeah excel sucks.

    I made @deebaser a program that does a similar fix. Not sure if it ever worked for him. I could probably whip something up for you as well.

    not a doctor, not a lawyer, examples I use may not be fully researched so don't take out of context plz, don't @ me
  • MahnmutMahnmut Registered User regular
    Ok so i'm trying to reconcile a mapping file given by my corporate overlords to our chart of accounts. Unfortunately, the full string they give us is broken up by periods (like so 515.73120.62200.1825.31). we have to take the periods out to get it to fit in the user defined field we use. seems pretty straight forward right? i pulled a list of all our accounts and the mapping we have into excel, and i'm trying to compare via VLookup to the mapping from corporate. i get the delimited string from the corporate sheet, copy that and paste special/values into a Text column, and then find and replace all "." characters with blank. Excel seems to like to round what it thinks is a number now, and display in scientific notation.
    so from 515.73120.62200.1825.31
    I get 5157312062200180000
    when i want 5157312062200182531

    I tried popping an A in front, and that keeps the exact number when i remove the "." characters, but when i find/replace the "A", i get the same result. However, if i go into the cell itself, and manually delete the A, it's fine, and i get the green "want to change this into a number" flag on the cell. but that's not feasible b/c i have 3,000+ rows.

    Anyone have any ideas?

    Each cell has a format setting -- you can find it by selecting and right clicking. You can probably select everything and pick the autoformat that doesn't format?

    Steam/LoL: Jericho89
  • Dr. FrenchensteinDr. Frenchenstein Registered User regular
    edited January 2013
    @bowen i think i'm just going to run a pull from our system again, and throw an A in front of what we have to make them match. So don't go nuts.

    Dr. Frenchenstein on
  • TheungryTheungry Registered User regular
    If you're willing to try a temporary workaround, sometimes when I run into this category of data formatting confusion on a one-time project, I try copying the data into a different spreadsheet (say google docs) manipulating it there, and then pasting it back into Excel. If the Data is clean enough, sometimes you can paste it into a notepad/text document and back out from there, and that will purge absolutely all undesired formatting while preserving all the data.

    Unfortunately, western cultures frown upon arranged marriages, so the vast majority of people have to take risks in order to get into relationships.
  • Dr. FrenchensteinDr. Frenchenstein Registered User regular
    edited January 2013
    Mahnmut wrote: »
    *SNIP*

    Each cell has a format setting -- you can find it by selecting and right clicking. You can probably select everything and pick the autoformat that doesn't format?

    Yeah i set all the cells in the column i copy/paste into to Text.

    Dr. Frenchenstein on
  • Dr. FrenchensteinDr. Frenchenstein Registered User regular
    Hmm, so i tried one more thing, if i left the A in there, took out the "."s, then made ANOTHER column, and used a LEFT formula to trim the A off, i got good data... I figured i was doing something dumb, but i guess not?

    I'm going to kick Bill Gates in the dick.

  • ecco the dolphinecco the dolphin Registered User regular
    Is there any possibility of using a formula like:

    =SUBSTITUTE( A1, ".", "" )

    ?

    So column A has the full "515.73120.62200.1825.31" text, and the column with the formula displays (at least for me) "5157312062200182531"

    ?

    Penny Arcade Developers at PADev.net.
  • Dr. FrenchensteinDr. Frenchenstein Registered User regular
    ooo, i've never used a substitute function before, that probably would work too... pretty classy Ecco!

  • Inquisitor77Inquisitor77 2 x Penny Arcade Fight Club Champion A fixed point in space and timeRegistered User regular
    edited January 2013
    Don't do this in Excel. You'll run into all kinds of weird memory buffer and cell formatting issues. Just do it in a text file then paste it all back in.

    (Notepad FTW)

    Inquisitor77 on
  • ihmmyihmmy Registered User regular
    can't you just do a find/replace and replace "." with ""?

  • WildEEPWildEEP Registered User regular
    Yeah don't do that in excel. Use Word. Copy the entire column with those entries in it and paste that column in word.
    Then do a FIND/REPLACE "." with ""
    (Important Final step) Then copy the result and paste it into Notepad first..then copy and paste it FROM Notepad, into the same column in Excel.

    Should work fine from there.

  • CycloneRangerCycloneRanger Registered User regular
    edited January 2013
    It sounds like this (http://office.microsoft.com/en-us/excel-help/format-numbers-as-text-HP010342535.aspx) is what you want to do. You need to manually set the formatting on the cells you're using (not just the ones you paste into, but the ones showing the results of any operations you perform) to "text" rather than the default (which will interpret any string that looks like a number as a number and round it).

    This really sounds like a job for Perl moreso than Excel, though. Parse everything into a couple of hashes, do your lookups, and then print out the results in whatever format you like.

    CycloneRanger on
  • TomantaTomanta Registered User regular
    This is one aspect of Excel that annoys me on an almost daily basis.
    WildEEP wrote: »
    Yeah don't do that in excel. Use Word. Copy the entire column with those entries in it and paste that column in word.
    Then do a FIND/REPLACE "." with ""
    (Important Final step) Then copy the result and paste it into Notepad first..then copy and paste it FROM Notepad, into the same column in Excel.

    Should work fine from there.

    Skip word and do the entire thing in notepad (CTRL+H for replace). In Excel, BEFORE you past anything in, select the entire column and format it as text. Then when you paste it in it should do it as text rather than a number. Should being the operative word.

    Alternatively, before fixing the formatting in Excel set it to text then do the find-replace there on that column, which I think will work in theory but Excel is so wonky at times who knows.

  • ArbitraryDescriptorArbitraryDescriptor changed Registered User regular
    +1 for the notepad pre-formatting step, but how are you "pulling" this data in the first place? Are you using a script you could modify to do the replacement when the data is read into memory?

  • Dr. FrenchensteinDr. Frenchenstein Registered User regular
    it's an export function of Dynamics/Great Plains. I'm not sure how corporate gets their data into excel.

    I did format the column to text, but for some reason it would still flip to scientific notation and round when i did the find/replace. i'll try the notepad suggestion next time i have to do this. thanks everybody!

  • TomantaTomanta Registered User regular
    it's an export function of Dynamics/Great Plains. I'm not sure how corporate gets their data into excel.

    I did format the column to text, but for some reason it would still flip to scientific notation and round when i did the find/replace. i'll try the notepad suggestion next time i have to do this. thanks everybody!

    Another thing you can do is format it as a number and set the decimal places to 0. Usually works for me. Why I couldn't say.

Sign In or Register to comment.