[email protected] +1.303.779.0344

Text Showing as Numbers

If you are grouping, sorting, and labeling by an Excel field in OnePager Express, but see that your text is showing up as a zero (0) instead of the letters shown in Excel, it could be that your Excel field is classified as numeric instead of as a string.

In OnePager Express, we automatically classify the types of data in your Excel fields as a part of the import process. If you have a mixture of different types of data in a single field, you may need to help OnePager determine how to classify it, if there's not a clear answer. Here's an example of an Excel spreadsheet with a field called "What Am I?" that consists of an equal mixture of letters (strings) and numbers:

If you import this into OnePager Express, it's not immediately clear whether this field is supposed to be a number or text, since there is a mixture of both. If OnePager classifies the field as numeric, then any text fields will be converted to zeros, like this:

Why does this happen? When you first import this field from Excel, OnePager Express looks at the first 30 rows in the spreadsheet. If the majority of the first 30 rows (16 or more) are text, OnePager treats the field as text. If the majority of the first 30 rows are numbers, OnePager treats the field as a number.

If you don't agree with how the field is classified, the easiest option is to re-sort your spreadsheet so that the majority of the first 30 rows are of the data type that you expect. In this case, if we want the field to be classified as a string, we want the majority (16 or more) of the first 30 rows to be a letter, not a number. A simple Excel sort will do this:

Now, save, close, and reopen the spreadsheet. This is an important step, because we want to give OnePager Express the opportunity to do a fresh examination of the spreadsheet in its new sort order.

Once re-examined, build another report, and you'll see that the field has been reclassified as a text field. All text values are showing as "a" now instead of "0":

Last Updated: December 27, 2019