Difficulty with number vs text format, especially when using primary column as match range

preston.murphy@vallourec.com

Currently I have a primary column in sheet 1 which is a value of 3 digits, all of which are numbers. These numbers are generated by the auto-number column type. I attempt to use this primary column as a match range to pull information with an index & match into sheet 2.

Performing a test using ISTEXT on sheet 1, all numbers in the primary column are indeed treated as text.

In sheet 2, for numbers up to 099, they are automatically considered text, but for anything greater than or equal to 100, they are considered as numbers. To correct this, I made another column and added a null string. I would expect the result to be that all of these values are now considered text and I can compare them easily to the primary column values in sheet 1. This is not the case. Even if the new column converts the numbers to text, when I write a formula in sheet 1 to compare the values, the values in sheet 2 are considered numbers even when referencing the column that was shown to be text. Understandably, this is detrimental to my process, as I am unable to find a way to guarantee that numbers greater than or equal to 100 are treated as text when referenced across sheets.

Furthermore, sheet 2 is populated by form entries. I thought if I include an apostrophe before the number when submitting the form, it may result in the number being recorded as text. Instead, it records it as text and includes the apostrophe at the beginning, rather than considering it as a number formatted as text. This means if you double click the cell, it actually has the number with two apostrophes before it.

Basically, it appears as if text vs number formats are not necessarily preserved when referenced cross-sheet, which I see as a big flaw. Is there any way to solve this, other than simply using an auto-number system with a non-numeric character? To me the fact that I cannot do it the current way, assuming I am not missing something, shows a bug/flaw in the software.

Thank you,

Dale Murphy

@preston.murphy@vallourec.com

I recommend either using numbers for matching, or using a unique key that begins with a letter to avoid any number to text translation.

tl:dr A few things:

A primary column cannot be a auto-number column type. Are you already using a formula to populate your primary column with whatever is in your auto-number column?

(SmartSheet is not Excel, the use of apostrophes does not have the same effect. I have found SS deadly consistent with the treatment of text and numbers BUT it does not behave the same as spreadsheet apps)

When I have a good reason for matching text, I will sometimes put a letter in front of a number string. Not only is this more visible, I can vary the letter(s) based on other conditions which aids downstream (e.g., Annn = brand A, Bnnn = brand B)

Finally, why do you need to match with text? Why not match with numbers?

dm

preston.murphy@vallourec.com

@Dale Murphy

Sorry, I misspoke about the auto-number being a primary column. Rather the column in sheet 2 is a primary column, which is filled by a form. The fact that the column is primary may be irrelevant. I am not sure. It may instead be a case of something weird happening when using a cross-sheet formula. I tested it within a single sheet and everything seemed to work as expected. It was certainly not behaving the same way when trying to using a cross-sheet formula.

Also, you actually can use an apostrophe to denote text. The apostrophe will not be displayed unless you double click the cell in which case it will reappear. Regardless, I do not like to use that. I prefer being able to explicitly assign a data type to column, rather than it being ambiguously either text or number in some scenarios.

I agree that including a non-numeric character in future auto-number columns would help, but I have been using the sheet for probably about a year before this issue became evident. That being said I can still probably use it. I just think it's concerning that I even need to work around it. The reason I used numbers is because it only seemed logical. There was no need for letters, and I wanted to make the values as short as possible. Since Smartsheet treats a number with a leading zero as text, once I had values high enough to no longer have leading zeros, this issue came into play.

Mike TV

@preston.murphy@vallourec.com

Try starting off your formula with =VALUE( and then the rest of the formula you'd normally write. That should force SmartSheet to treat what you're doing as a number even though it thinks it's text.

Ron_Sammons

I was having a similar issue and came up with a less than great solution but it does work. In Smartsheet, I added a column with a column formula that took the value of the column I want, and added something like "XXX" to the beginning of the value in the new column. Then once in Excel, I used Power Query to strip the "XXX" and rearranged to columns. This works.