Text values that look like numbers but are stored as text can cause problems in many situations. When you need to perform calculations and analysis on data, numbers stored as text will produce errors or incorrect outputs. Converting text to actual numeric values is an important step to prepare your data for analysis.
In this guide, we will cover common ways numbers can end up stored as text, problems this can cause, and different methods to convert text numbers to numeric values in Excel, Google Sheets, SQL, and other applications.
How do numbers become stored as text?
There are a few main ways numbers can end up stored incorrectly as text values:
Importing or copying data
When you import or copy data from another source into a spreadsheet or database, numbers may be converted to text in the process. This can happen due to:
– Inconsistencies between data types and formats across different applications. For example, copying data from a SQL database to an Excel spreadsheet.
– Errors in the import or copy process that fails to detect numbers stored as text.
– Intentionally storing numbers as text when importing, to preserve leading zeros or special formatting.
Manual data entry
If you manually enter data into cells, formulas may interpret your numeric inputs as text instead of numbers in some cases. This can happen if:
– You prepend zeros to numbers, like entering “08” instead of “8”. Leading zeros indicate a text value.
– You enter numbers with symbols or formatting, like “1,000” or “(425) 555-1234”. The commas and symbols are text characters.
– You enter numbers with leading apostrophes, like `’15`. The apostrophe forces the cell to contain text.
Converting numbers to text
In some cases, numbers may be intentionally converted to text with functions like TEXT() in Excel. This is done to change their formatting or preserve leading zeros and special characters. But if left as text, this can lead to issues down the line.
Problems caused by numbers stored as text
When numbers are stored as text instead of numeric values, you may encounter problems like:
Errors in calculations
Formulas that perform math on the text values will produce errors or invalid outputs. For example, summing a column of text numbers will concatenate them instead of adding them.
Incorrect sorting
Trying to sort text numbers will arrange them alphabetically instead of numerically. So 10 would come after 1, because “1” comes before “10” alphabetically.
Difficulty aggregating data
It becomes difficult to properly aggregate and analyze your data mathematically. Text values often need to be converted to numbers first before you can calculate metrics like sums, averages, etc.
Improper charting and visualization
Any visualizations and charts created from text numbers will be incorrect or misleading, since they are not treated as actual numeric values.
How to check if data contains numbers stored as text
To avoid or fix issues caused by text numbers, the first step is identifying cells that contain numbers stored as text. Here are ways to check in different applications:
In Excel
Excel provides several ways to test for numbers stored as text:
– Try to perform math on cells, like adding them. Text values will return errors.
– Use the ISTEXT() function. It returns TRUE if the cell contains text.
– Try converting to a number with VALUE(). If no error, it’s stored as text.
– Text alignment is left-justified by default in cells. Numbers are right-justified.
– The cell format is italicized if it contains text versus numbers.
In Google Sheets
Similar to Excel, you can test for text numbers in Google Sheets by:
– Attempting calculations. Text values will show errors.
– Using the ISTEXT() function. Returns TRUE if text is detected.
– Text alignment is left-justified instead of right-justified for numbers.
– Conditional formatting can apply color formatting to text.
In SQL databases
For SQL tables and queries, methods to test for text numbers include:
– Cast or convert columns to numeric data types. Errors will occur on text values.
– Use functions like ISNUMERIC() or TRY_CAST() to test if conversion is possible.
– Check the data type and length of columns. Text has type VARCHAR and length exceeding numbers.
– Query and aggregate columns using math. Text values will be skipped in sums or averages.
In programming languages
When working with text numbers in code, you can check using:
– Type checking functions like isnumeric() in Python or is.numeric() in R.
– Casting or converting variables to numeric types and handling potential errors.
– Regular expressions to test if strings contain only numeric digits.
– Parsing strings as numbers and catching text values with exception handling.
Methods to convert text to numbers
Once you’ve identified cells, columns, or values containing stubborn text numbers, there are various ways to convert them to numeric formats depending on your application:
In Excel
Excel offers several options:
– Use the VALUE() function to convert a text value to a number.
– Set the cell format to Number or General to convert to a numeric format.
– Use Paste Special to multiply cells by 1, which forces numeric values.
– Import or open the file in Text format, specifying column data types as numeric.
– Use the Text to Columns wizard, setting numeric columns to the Number format.
In Google Sheets
Google Sheets can convert text numbers by:
– Using the VALUE() function to convert text strings to numbers.
– Setting the cell format to Number.
– Multiplying cells by 1 to convert to a number format.
– Importing data and specifying column data types as numbers versus text.
In SQL databases
For SQL tables, you can convert columns of text numbers by:
– Using CAST() or CONVERT() to cast the column to a numeric data type like INT or DECIMAL.
– Parsing the text values as numeric types in SELECT queries.
– Updating the table definition to change columns from VARCHAR to numeric types.
– Importing data with specified numeric column types versus text.
In programming languages
To handle text numbers in code, use:
– Type conversion functions like int() and float() in Python.
– Numeric classes like as.numeric() in R.
– Parsing strings as numeric types and catching exceptions.
– Regular expressions to remove non-numeric characters before converting values.
– Built-in functions like ValueError in Python to catch invalid conversions.
Tips for importing, storing, and formatting numbers
To avoid ending up with numbers stored as text in the first place, here are some useful tips:
– When importing data, explicitly specify column types as numeric.
– Remove symbols and formatting like commas during import to avoid text values.
– Be consistent in how numbers are stored and formatted across systems.
– Don’t prepend zeros to numbers. Use formatting to add them back after import.
– Avoid using apostrophes or quotes around numbers that can force text formats.
– Set number formatting only after importing values as numbers.
– Use leading zeros only when required, such as ZIP codes.
– Test imports with summary info to confirm columns contain expected numeric datatypes.
Conclusion
Dealing with numbers stored incorrectly as text is a common data issue that can wreak havoc in analysis and calculations. But Excel, Google Sheets, SQL, and other applications provide multiple functions to identify these tricky text numbers and convert them to the proper numeric formats. With the right awareness and tools, you can wrangle even the most unruly text numbers into shape.
| Application | Method to Check for Text Numbers | Method to Convert Text to Numbers |
|---|---|---|
| Excel | – ISTEXT() function | – VALUE() function |
| Google Sheets | – Left-justified alignment | – Multiplying by 1 |
| SQL | – ISNUMERIC() function | – CAST() or CONVERT() functions |
