Microsoft® Excel removes leading zeros from a field unless the text is surrounded by quotations - for example, "00001" becomes "1" . Leading zeros are often seen on item codes, vendor codes, and UPC codes. Follow the steps below to create a formula that adds leading zeros to a column in MS Excel. This is helpful when importing data into Evosus Enterprise.
Add leading zeros to a column in MS Excel
The example below references UPC codes specifically, but can be used for any fields with leading zeros.
- Open MS Excel.
- Make sure the UPC column is empty except for the column header.
- Go to the last column of your spreadsheet.
- In the second field of the first empty column, enter a " . This will most likely be field T2.
- In the second field of the second empty column, enter a 0 . This will most likely be field U2.
- Starting in the second field of the third empty column, enter all the UPC codes associated to the items. The starting field will most likely be V2. Each UPC code should be listed in the same row as the corresponding item.
- Go to the UPC column. In the second field, enter the following formula: =T$2&U$2&V2&T$2.
- If your columns are not T, U and V, then you will need to change the letters in the formula.
- Press <Enter> on the keyboard to calculate the formula. The field should have added a quotation and zero to the front of the UPC code and a quotation to the back.
- To copy the formula to the rest of the rows, place your mouse over the lower right corner of the field where you just entered the formula. The cursor should turn into a +.
- Click the lower right corner and drag the mouse down to the last row with data. The formula will be applied to each UPC code and the appropriate quotations and zeros will be added.
Please note that in the screenshot below, several columns are hidden in order to show all relevant fields on the same screenshot. Your spreadsheet columns will appear slightly differently.
Unless explicitly stated, this topic documents the most recent version of Evosus Business Enterprise. If you're not using the latest version, go here to download it.
Did you find it helpful?Send feedback