Sage 100

Does anyone know why when exporting a lookup to Excel the leading zeroes are dropped? For instance, SO numbers the leading zeroes are dropped, same with inventory item numbers? Even if you save the Excel lookup as a .csv file the leading zeroes are not there (I opened in Notepad to verify).

Been like that forever. Same thing with zip codes. Figured Sage would have fixed that by now; silly me.

@JaneScanlan Excel does that if it determines the values in that column are numeric. As soon as the data hits excel. You can import a text file into Excel and specify that the column is text but that doesn't apply in this case. You might want to use vi or crystal to get the data out to a text file and avoid excel until after you have it extracted.

Dang, I thought there would be some sort of setting to fix this. Thanks for the response @BrettZimmerman

@JaneScanlan it might be easier to just use a excel query instead of exporting the data. Then you don't have to deal with the missing zeroes.

Thanks for all the advice, the client likes the feature of clicking the Excel button from a lookup, and they aren't too sophisticated to use MSQuery. Oh well.

Major reason I held onto Lotus for so long. Didn't drop the leading zeroes... Then I found my true love with Queries and Pivot Tables. Lotus who????

Remember Visicalc?

They had that in your AP kindergarten class???

MS Multiplan?

We had visicalc on one machine when I was in high school. They also had Lotus 1-2-3 1.x on one machine. They were both really old machines.

Cool. I think I still have VisiCalc for my TRS-80 Model 1.

@robertwood They had to be really old machines, if you saw them. At least as old as me - I started on Viscalc on Apples. #visicalc #/replicate

I think it was an old TRS machine.

Those are the Radio Shack ones, right?

Sounds like you're just showing off. Not only did you have computers in high school but you even had old computers! My idea of Hi-Tech in high school was my TI-55!

Yes, Radio Shack Trash 80 - the Kia of original microcomputers. (Apologies to Kia drivers, but I knew I'd get in trouble with Honda Accord people.)

@JaneScanlan after exporting a lookup list to Excel, I highlight the column that needs left zero filled values, select Format Cells, select Custom and in the custom field enter a full mask of zeros to match the field length. For example if the leading zeros drop from the Sales Order number enter seven zeros in the Custom Field mask.

Yes, that works perfect for SO numbers, but not inventory item numbers.

Moira got a head of me by about 20 minutes. For inventory, about the only way I know is to print to an Ascii file instead of going directly to excel. Then open the data tab in Excel and import the ascii file to Excel. You get the opportunity on the last step to change the data type for the item code from General to Text and leading zeros come in. Maybe a bit much for your user but it's pretty easy after they get through it a couple times..