How to convert to number in excel

What is text-to-number conversion in Excel?

Text-to-number conversion in Excel is the process of transforming text-stored numeric values into true numeric data types. This conversion addresses common issues like import errors, apostrophe prefixes, or pre-formatted text cells that prevent calculations from working correctly.

How to convert text to numbers using the error checking icon

Click the green warning icon that appears next to cells containing numbers stored as text and select "Convert to Number" from the dropdown menu. This method provides instant conversion for flagged cells.

Excel displays a small green triangle in the corner of cells where it detects numbers stored as text. The exclamation mark icon appears when you select these cells, offering quick access to the conversion option without formulas or additional steps.

How to convert text to numbers using Text to Columns

Select the range containing text data, navigate to Data > Text to Columns, choose Delimited or Fixed Width, click Next twice, then click Finish. This process forces Excel to reparse the content as numbers without changing the underlying values.

Text to Columns works by reinterpreting the cell contents through Excel's data parsing engine. The method handles bulk conversions efficiently and requires no helper columns or formulas.

How to convert text to numbers using Paste Special

Enter the number 1 in an empty cell and copy it using Ctrl+C. Select the text range you want to convert, right-click, choose Paste Special > Multiply, and click OK. Multiplying by 1 coerces text values to numbers while preserving the original numeric values.

This technique leverages arithmetic operations to force type conversion. The original cell locations retain the converted numbers, eliminating the need for separate output columns.

How to convert text to numbers using formulas

Use the VALUE() function by entering =VALUE(A1) in a new cell to convert text in cell A1 to a number. Copy the results and paste as values to replace the formula with static numbers. Arithmetic coercion using =A1*1, =A1+0, or =--A1 achieves the same result with simpler syntax.

VALUE() function

VALUE() serves as a general-purpose converter that handles most conversion scenarios, including text representations of dates and currency symbols. The function returns a numeric value from any text string that Excel can interpret as a number.

NUMBERVALUE() function

NUMBERVALUE(text, [decimal_separator], [group_separator]) converts text to numbers with explicit control over locale-specific formatting. European-style numbers like "1.234,56" convert correctly by specifying "," as the decimal separator and "." as the group separator.

This function prevents misinterpretation errors when working with international data formats where decimal and thousand separators differ from system defaults.

Arithmetic coercion

Simple operations like =A1*1, =A1+0, or =--A1 force Excel to interpret text strings as numbers. These lightweight formulas work well for quick inline conversions without requiring additional helper columns.

Text parsing with conversion

Combining text parsing functions (LEFT(), RIGHT(), MID()) with arithmetic operations extracts numeric substrings from mixed-content cells. Adding +0 to the parsed result converts the extracted text to a number.

Why are numbers stored as text in Excel?

Numbers get stored as text in Excel due to data import issues, formatting choices, and manual entry errors.

Data imports

Copying from external sources like CSV files, Access databases, or websites often pastes numbers as text. The source system may treat values as strings or use incompatible delimiters that Excel cannot automatically interpret as numeric.

Text formatting

Cells pre-formatted as Text before data entry force numeric-looking values to store as strings. Leading apostrophes ('123) enforce text mode regardless of the cell's format setting.

Mixed data types

Columns containing numbers mixed with text characters (codes like "s2.54") or non-standard separators (commas as decimals in regions using periods) prevent Excel from recognizing values as numeric.

How to identify numbers stored as text in Excel

Excel provides multiple indicators to identify cells containing numbers stored as text.

Green triangle alert

Excel flags affected cells with a green triangle in the upper-left corner. Clicking the exclamation icon reveals the message "Number Stored as Text" along with conversion options.

ISNUMBER() formula

Enter =ISNUMBER(A1) to test whether a cell contains a true number. The formula returns FALSE for text-stored numbers and TRUE for numeric values. Apply this formula across a range to check multiple cells simultaneously.

Cell alignment

Numbers right-align by default in Excel cells. Left-aligned values that appear numeric indicate text storage.

Formula behavior

SUM functions and arithmetic operations ignore text-stored numbers or return errors. True numeric values participate in calculations without issues.

What are the limitations of converting text to numbers?

Converting text to numbers in Excel carries risks of data loss, errors, and unintended formatting changes.

Data loss risks

Non-numeric text like codes ("ABC123") or mixed strings return #VALUE! errors when processed through VALUE() or arithmetic coercion. Overwriting original data with error results causes permanent data loss.

Trailing spaces or hidden characters such as non-breaking spaces from imports cause #VALUE! errors during conversion. The TRIM() function removes standard spaces, but CLEAN() or SUBSTITUTE() may be necessary for non-breaking spaces.

Formatting considerations

Dates and times stored as text (such as "12/03/2025") convert to serial numbers (such as 45272). The results display as plain decimals unless you apply date formatting to the cells.

NUMBERVALUE() produces incorrect values when separator parameters do not match the actual data format. The string "1,234.56" converts to 1.23456 without proper parameter specification.

Text to Columns and Paste Special methods alter original data irreversibly. Large datasets may experience recalculation delays during bulk conversion operations.

Mitigation practices

Duplicate columns before performing bulk conversions to preserve original data. Test conversion formulas on sample cells before applying to entire ranges. Verify results using ISNUMBER() after conversion to detect silent failures where values did not convert properly.