Our parent company is requiring that we use their Customer ID system in E10. And their customer ID’s are a 7 digit “number” with leading zeros. Like 0001234.
0001234isn’t a number!!!
Almost as bad is another place I’ve worked had a part number system consisting of 2 digits, a dash, then 4 digits: Ex: 34-0123. Now I have no issue with that, but people would say,
“We’re out of part number ‘thirty four, dash one twenty three’…”
Or more often just
‘thirty four, one twenty three’
34-123 is not a part number!!!
And god help you if you need to pull these into excel. As it does you the “favor” of converting to numbers.
Well the Post Office led the way with ZIP codes on that one.
But yeah our supplier numbers are that way, with leading zeroes. I actually said when we were switching over to Epicor 4 years ago “You know, this would be a good time to put a V or something in front of these numbers so we never have this issue again.” But I was shot down. Now I know better. Don’t ask, just do it and shrug your shoulders later.
Yeah, for some odd reason, we have ship tos set up that way… with leading zeros. If I had a dime for every time I’ve had to go into Excel, paste data in, change the format of the column to text, and then repaste…
Like Gnu’s Not Unix or Yaml Ain’t Markdown Language or Lame Ain’t an MP3 Encoder… mine’s even better: VIN Ain’t a Recursive Vehicle Identification Number Number.
Don’t even get me started with Excel… that stupid “smart” formatting has been the bane of my existence for years… try doing vlookups for identical fields, only to discover that one sheet has it formatted as numbers, and the other sheet as text. If you can use the “Export to Excel” functionality from a grid, it holds onto the leading zeros nicely… but good luck with reports to excel or csv that then open up in Excel.
I wish Microsoft would quit trying to be so proactive with guessing at how you might want something formatted. That “General” default option for formatting is a killer. Would be better if they opened up a message box upon pasting that would ask you to choose how you want each column formatted.
Always in the same boat as you are with pasting, formatting specific columns, and then repasting.
A simple allowing for leading zeros in numeric values would do wonders.
Yeah, you can do vlookup with TEXT, so it’s something like
=VLOOKUP(TEXT(A2, "@"), Sheet2!B:C, 2, false)
But I hardly ever do that, because vlookup is long enough already. (False - WHY? When do I NOT want an exact match? And can we just assume I want the LAST column as a return value? I could specify a different one if I felt like it.)
Someone taught me a few years ago to use text-to-columns on one of the columns and convert it to text that way. Much faster than anything I had come up with.
Us old timers that started in COBOL know that your leading zeros rant isn’t really justified if you think it is someone’s intentional design decision. Rather it is from a requirement of the language of the time. When I see a company that is using numbers that begin with leading zeros I know that company system started out in COBOL. May very well still be using COBOL. COBOL didn’t have the dynamic sizing of our current day languages. We had to define a field to the largest character count it should hopefully never get too. A new record then filled that field. Since numbers are right aligned you are padding zeros up front.
Well… I got burned once when I put a leading 0 in my C code - I had a multidimensional array of of numbers, and decided to pad them out with zeros so they’d line up in the code.
But ANSI C treats numbers with leading zeros as octal. if(010 != 10), is false
while if(010 == 8), is true
