Dynamics GP Tip: Understanding Excel and Leading Zeros

Improve the way you view data by understanding Excel.

Leading zerosSome fields in Microsoft Dynamics GP may contain data with leading zeros. For example, a Vendor ID may be recorded as 0001234. If this data is exported from GP as a CSV file, then opened in Excel, the leading zeros will be stripped out. This is true for a file exported from any source (not just GP).

 

The above CSV file was opened in Excel, and the leading zeros in the first field are gone.

missing leading zeros

To prevent this, rename the file to a .txt file. For example, Sample CSV.csv was renamed to Sample CSV.txt. Then open the file in Excel. This will launch the Text Import Wizard. Choose Delimited as the file type that best describes your data and click Next.

missing leading zeros fix

Choose Comma as the delimiter, then click Next.

leading zeros fix

Highlight the column in the “Data preview” section that contains the data with leading zeros, and then select Text as the column data format.  Repeat for each column that contains data with leading zeros.  Then click Finish.

gp data preview

gp leading zeros retainedWhen the file opens in Excel, the leading zeros are retained.

Find more Tips & Tricks for using Microsoft Dynamics GP here.

Success StorySee Client Success Stories


“Our Socius team, in my eyes, is considered an extension of our own department. Their knowledge and professionalism provide the support we need to succeed and continue to grow.”
-John Keller
LPK in Cincinnati, OH

eBookRead Top eBooks and Whitepapers