CURSE YOU EXCEL AND HOW YOU HANDLE TEXT/NUMBER FIELDS

Dr. Frenchenstein

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?

Bowen

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.

Mahnmut

Dr. Frenchenstein wrote: »

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?

Dr. Frenchenstein

@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.

Theungry

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.

Dr. Frenchenstein

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

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 dolphin

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"

?

Dr. Frenchenstein

ooo, i've never used a substitute function before, that probably would work too... pretty classy Ecco!

Inquisitor77

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)

ihmmy

can't you just do a find/replace and replace "." with ""?

WildEEP

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.

CycloneRanger

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.

Tomanta

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.

ArbitraryDescriptor

+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. Frenchenstein

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!

Tomanta

Dr. Frenchenstein wrote: »

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.