Data Storage Conflict
I created a sheet by importing from an excel file. The primary column (EmployeeID) was stored as text upon import even though it was stored as "number" in the excel file.
Subsequent data input from either a Form or manual input into the sheet is being stored as a number. Sorting the column with the mixture of text and number produces undesirable results. Is there a way to force the sheet to store the data as either one or the other?
With most of the data stored as text that would be my preference as it appears that there is no easy solution to convert text to number. It seems you have to manually strip the hidden apostrophe from the text.
Looking foward to hearing about possible solutions.
--cd.
Best Answer
-
You need to use either "@row" or the row number. @row tells the formula to evaluate [Column Name] on whatever row the formula is in. It is a little more efficient on the back-end.
=VALUE([SID]@row)
OR
=VALUE([SID]2)
As for your initial attempt, copying the information into a temp column from the primary column will copy the data exactly to include the format. That is why I suggested using the VALUE function to convert everything to numbers FIRST then copying that.
Answers
-
You could create a helper column (that can later be hidden to keep your sheet clean) and use
=VALUE([Primary Column]@row)
This will convert everything into a numerical value which will provide for consistent sorting.
Another option would be to do the same thing but then copy the column data and use "paste special" to paste the values into the Primary column. This will allow you to get rid of the helper column entirely and shouldn't have to be repeated since additional form entries are populating as numbers already.
-
Thanks Paul,
I appreciate the response.
I previously created a temporary column and copied the information in the primary column and used "paste special" to paste the value of the primary column into the temporary column. However, the data was stored in the temporary column as text. That doesn't seem intuitive.
I tried your formula =VALUE([Primary Column]@row) but don't understand the parameters.
=VALUE([SID]@2) where SID = the name of the Primary Column and 2 = the row, this results in #UNPARSEABLE.
Let me know what I'm doing wrong.
Thanks
-
You need to use either "@row" or the row number. @row tells the formula to evaluate [Column Name] on whatever row the formula is in. It is a little more efficient on the back-end.
=VALUE([SID]@row)
OR
=VALUE([SID]2)
As for your initial attempt, copying the information into a temp column from the primary column will copy the data exactly to include the format. That is why I suggested using the VALUE function to convert everything to numbers FIRST then copying that.
-
Thanks Paul,
Worked
-
Happy to help. 👍️
