Converting the Data Type of Existing Data

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:

From To Behavior Example From Example To
Attachment

Text

Files will convert to the file name(s) of the attachment(s). mceclip0.png mceclip2.png

URL, User, SelectEmail, and other data types

Text

Previous data will convert to the matching text. mceclip0.png mceclip1.png

URL, User, SelectEmail, and other data types

Number

Cells that are only numeric will be preserved. If Strict is not selected, non-numeric text will also be preserved. mceclip1.png mceclip3.png

Multiselect

Number

Cells that are numeric will be preserved. However, only the first selected option will convert to a number. mceclip5.png mceclip4.png
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. mceclip2.png mceclip3.png
User Text, Automatic, and other data types User values get converted to the user's name (First name [space] Last name). mceclip0.png mceclip4.png
User Email User values convert to the user's email address. mceclip1.png mceclip5.png
TextNumberURL, 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. mceclip3.png mceclip2.png
TextNumberURL, 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. mceclip5.png mceclip4.png
Rating TextNumber, and other data types Preserves the rating you provided. mceclip6.png mceclip7.png
TextNumber, and other data types Rating Converts from numbers to the designated rating mceclip8.png mceclip9.png
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. mceclip10.png mceclip11.png
Checkbox Text, Automatic, Number, and other data types Boxes are converted to the boolean "true" or "false". mceclip11.png mceclip12.png
Text, Automatic, Number, and other data types Icon Set Values matching the symbol name will be used to convert to the appropriate symbol. mceclip13.png mceclip14.png

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.

From To Example From Example To
Text, Automatic, Number, and other data types Related Row mceclip4.pngmceclip1.png  mceclip5.png 

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