When the data type of a cell is changed, Spreadsheet.com converts existing data to the new data type
Spreadsheet.com goes beyond text and numbers with support for over 25 rich data types that you can choose from at the cell, range, and column level. You can change the data type of any cell, column, or range of cells. When the data type of a cell is changed, Spreadsheet.com converts existing data to the new data type. Below are some examples of how your data might change when converted.
For more information on different data types, check out our Introduction to Data Types. Or, to learn how to change data types, check out our Changing Data Types article.
Data Conversion Behavior
When converting between data types, Spreadsheet.com tries to retain your data. For example, if you switch a cell from a Number data type to a Text data type, Spreadsheet.com retains the digits, but changes to text formatting (bottom and left alignment).
However, many of the conversions from one data type have no obvious corollary in a new data type. For instance, if you convert a cell from a date data type to an attachment data type. In cases like these, Spreadsheet.com empties the cell.
You can always revert the conversion by selecting Undo from the top left corner of the toolbar, or by using the keyboard shortcut Ctrl + Z (CMD + Z for Mac users).
What happens to Formulas when converting data?
Converting cells or columns with formulas may replace the formula with the output of the formula, especially when you restrict the column or cells to a data type. For example, converting a column with values and a sum at the end (like
=Sum(A1:A10)) to the Number Data Type will replace that formula with the formula's output.
Example Data Type Conversions
While some conversions empty the cells, other conversions retain some part of the data. Some examples of conversions include:
|Attachment||Files will convert to the file name(s) of the attachment(s).|
|Previous data will convert to the matching text.|
|Cells that are only numeric will be preserved. If Strict is not selected, non-numeric text will also be preserved.|
|Cells that are numeric will be preserved. However, only the first selected option will convert to a number.|
|Text, Automatic, and other data types||User||Spreadsheet.com looks for both email and name (First name [space] Last name) matches to do the conversion.|
|User||Text, Automatic, and other data types||User values get converted to the user's name (First name [space] Last name).|
|User||User values convert to the user's email address.|
|Text, Number, URL, User, Email, and other data types||Select||Each distinct cell value is converted into a select option and cells are assigned the select option they previously contained.|
|Text, Number, URL, User, Email, and other data types||Multiselect||Each distinct cell value is converted into a select option, including comma-separated options. Cells are assigned the select options corresponding to the values they previously contained.|
|Rating||Text, Number, and other data types||Preserves the rating you provided.|
|Text, Number, and other data types||Rating||Converts from numbers to the designated rating|
|Text, Automatic, Number, and other data types||Checkbox||Boolean values "true" and "false" are converted to checked and unchecked boxes, respectively. "1" and "0" are converted to checked and unchecked boxes.|
|Checkbox||Text, Automatic, Number, and other data types||Boxes are converted to the boolean "true" or "false".|
|Text, Automatic, Number, and other data types||Icon Set||Values matching the symbol name will be used to convert to the appropriate symbol.|
Related Row Conversions
When converting to a Related row data type, Spreadsheet.com will attempt to match existing values with values from the primary column of the target worksheet to automatically establish relationships.
When this conversion happens, commas (,) are considered separators so converting "North,South" to a related row cell would result in an attempt to map two separate values "North" and "South" to respectively named rows in the target worksheet.
|Text, Automatic, Number, and other data types||Related Row|
Check out our article on the Related row data type for more information, or our series of articles on Related rows.
Converting Existing Data to A Restricted Data Type
When converting existing data to cells, columns, or cell ranges with Strict Columns and Cells, Spreadsheet.com requires all existing data to match the new data type, or the data is emptied. For example, converting a range of cells that contain include text and numbers to a Number data type will empty cells that contain text (even if a given cell also contains numbers).
If you try to convert or change data types of cells that are already restricted you will get an error message and must adjust your selection before being able to proceed.
Learn more about converting between data types in our article on Changing a Column's Data Type. Or, read on to learn more about the rest of Spreadsheet.com's Data Types