How to Convert Text-Formatted Numbers to Real Numbers in Google Sheets Using VALUE

One of the most frequent frustrations when importing financial reports, CSV exports, or web-scraped tables into Google Sheets is encountering numbers stored as text strings. When numerals are formatted as text, Google Sheets treats them as alphabetical characters: mathematical formulas like SUM, AVERAGE, and MAX return zero or ignore the cells entirely, while comparison operators fail to sort correctly. To resolve this formatting disparity, Google Sheets includes the VALUE function, which parses text representations of numbers into genuine serial numeric values.

Recognising Text-Formatted Numbers

You can identify numbers masquerading as text strings through visual cues and diagnostic formulas:

• Left Alignment: By default, Google Sheets automatically aligns genuine numeric data to the right edge of a cell and text strings to the left. If a column of numbers hugs the left margin, it is stored as text.
• Apostrophes: Imported datasets frequently prefix numbers with an invisible leading apostrophe ('1250), which forces Sheets to treat the input as literal text.
• The ISNUMBER Test: Enter =ISNUMBER(A2) in an empty cell. If the formula returns FALSE, the cell contains text rather than a real number.

Syntax and Functionality of the VALUE Function

The VALUE function converts a string that appears in any standard number, date, or time format into a real number:

=VALUE(text)

The function seamlessly parses various formatting notations, including currency symbols ($1,250.50), percentage signs (15%), scientific notation (1.5E+03), and thousand-separator commas.

Step-by-Step Instructions: Applying the VALUE Function

To convert a column of text numerals into functional calculation values:

1. Open your spreadsheet in Google Sheets.
2. Select an empty adjacent column (for example, column B next to text column A).
3. In cell B2, enter the formula:

   =VALUE(A2)

4. Press Enter. Google Sheets strips the text formatting and outputs a true numeric value aligned to the right.
5. Double-click the blue fill handle at the corner of cell B2 to extend the formula down the entire dataset.
6. Verify formula arithmetic by testing =SUM(B2:B); the sum will now calculate accurately.

Array Processing with ARRAYFORMULA

Instead of manually dragging formulas down thousands of rows, wrap the VALUE function in an ARRAYFORMULA to process dynamic ranges automatically:

=ARRAYFORMULA(IF(A2:A="", "", VALUE(A2:A)))

This single formula in row 2 converts every incoming record in column A without requiring manual expansion.

Instant In-Place Conversion with Paste Special

If you want to convert numbers in-place without generating a helper formula column:

1. Type the number 1 into any blank cell and press Ctrl + C to copy it.
2. Highlight your column of text-formatted numbers.
3. Right-click the selection, hover over Paste special, and select Paste values only > Multiply (or press Ctrl + Alt + V).
4. Multiplying text strings by 1 forces Google Sheets to evaluate the mathematical expression, instantaneously coercing all text into genuine numbers in-place.