What can I do if Xcelsius is chopping off the leading zero’s in my order numbers?
This question comes up often when I’m teaching an Xcelsius training class. Often enough that I’ve decided to discuss the solution here in a tech tip on our blog.
This scenario can happen with different data sources (XML, Live Office, Qaaws) when a number string is interpreted by Xcelsius to be numbers rather than string data. As many people have tried, it is not enough to format the cells as text in the Excel spreadsheet of Xcelsius. We need to use a trick to add those leading zeros back in.
For example, say my Order numbers are all 3 digit numbers (for simplicity sake). My order numbers are loaded into Column D, but then converted to their proper 3 digit format in Column F with the use of the Length formula in Excel, LEN. By simply combining an if statement and the Length function to determine the length of a value, we can add the appropriate number of zero’s in front of a value, and voila, properly formatted Order numbers!
See example below: