Data Conversion in Google Sheets: N, VALUE, and Related Functions

August 7th, 2026

Imported CSVs love to deliver numbers as text, dates as strings, and percentages as labels. Math then fails in quiet ways: SUM skips text-numbers, lookups miss, charts treat categories as values. Google Sheets gives you a conversion toolkit — VALUE, N, TO_TEXT, TO_PERCENT, TO_PURE_NUMBER, DATEVALUE, and TEXT — to coerce types deliberately. This pairs with Data Cleaning, Sorting, and Analysis Tips in Google Sheets and the date-focused Normalize Dates in Google Sheets with DATEVALUE and TEXT.

Why type coercion matters

| Cell looks like | Actual type | =A2*2 | | --- | --- | --- | | 42 (number) | number | 84 | | '42 or "42" from CSV | text | often works via auto-coerce, until it does not | | 42% formatted | number 0.42 | 0.84 | | 2026-08-07 as text | text | #VALUE! in date math |

Do not rely on Sheets “sometimes” converting text. Convert at the edge of the import, then calculate on clean types.

VALUE — text to number

VALUE parses a numeric string into a number:

=VALUE("42")
=VALUE(A2)
=VALUE(TRIM(A2))

Useful after CSV import when amounts are left-aligned text. Combine with TRIM and CLEAN first:

=VALUE(TRIM(CLEAN(A2)))

If the string is not numeric, VALUE returns #VALUE! — wrap with IFERROR when a blank fallback is appropriate:

=IFERROR(VALUE(A2), )

N — value to number, with clear rules

N converts using a small rule set:

| Input | N result | | --- | --- | | Number | the number itself | | Date | date serial number | | TRUE | 1 | | FALSE | 0 | | Text | 0 | | Error | the error propagates |

=N(TRUE)
=N(A2)
=N(DATE(2026,8,7))

N is ideal when you need booleans as 0/1 in arithmetic:

=SUM(ARRAYFORMULA(N(E2:E)))

That counts checked checkboxes if E holds TRUE/FALSE. Note: text "123" becomes 0 under N, not 123 — use VALUE for numeric strings.

TO_PURE_NUMBER — strip presentation, keep magnitude

TO_PURE_NUMBER turns dates, percents, and currency-formatted values into plain numbers:

=TO_PURE_NUMBER(A2)

Examples of intent:

• 25% → 0.25
• $1,500.00 → 1500
• a date-time → its serial (e.g. 44625.4375)

Use when formatting is getting in the way of joins or exports that expect raw scalars.

TO_PERCENT and TO_TEXT

TO_PERCENT converts a number to a percent value (and percent formatting):

=TO_PERCENT(0.45)
=TO_PERCENT(A2)

0.45 becomes 45%. Prefer this over manually multiplying by 100 and typing % when you want Sheets to keep the underlying 0.45 for math.

TO_TEXT forces a text representation:

=TO_TEXT(A2)
=TO_TEXT(1.2)

Handy before concatenation or when an API-bound column must be text even if it looks numeric (IDs with leading zeros are still better served by apostrophe-prefix or TEXT with a format — see below).

DATEVALUE and TEXT — dates as data vs labels

DATEVALUE parses a date-looking string into a real date serial:

=DATEVALUE(A2)
=DATEVALUE("2026-08-07")

TEXT goes the other way: take a number/date and produce a formatted string:

=TEXT(A2, "yyyy-mm-dd")
=TEXT(B2, "0.00%")
=TEXT(C2, "00000")

The last example preserves leading zeros for codes. Full walkthrough: Normalize Dates in Google Sheets with DATEVALUE and TEXT.

Practical patterns for messy imports

Amount column that arrived as text

=ARRAYFORMULA(IF(A2:A="",, IFERROR(VALUE(REGEXREPLACE(A2:A, "[^0-9.\-]", "")), )))

Strips currency symbols and spaces, then coerces. Adjust the regex if you use European decimal commas (normalize commas first).

Percent labels like "12.5%" as text

=IFERROR(VALUE(SUBSTITUTE(A2, "%", ""))/100, )

or, if Sheets already parsed them as percents, TO_PURE_NUMBER is enough.

Checkbox / Yes-No to 0-1

=N(A2=TRUE)
=N(UPPER(TRIM(A2))="YES")

Keep SKU as text when Excel-style imports drop leading zeros

=TEXT(VALUE(A2), "000000")

Only when the SKU is purely numeric; otherwise keep the original text and force the column format to Plain text before pasting.

Date string → serial → Unix (optional chain)

=(DATEVALUE(A2) - DATE(1970,1,1))*86400

See Unix Timestamps and Conversion in Google Sheets for epoch details.

VALUE vs N vs TO_PURE_NUMBER

| Function | Best for | Watch out | | --- | --- | --- | | VALUE | Numeric text → number | Errors on non-numeric text | | N | Booleans, dates → number; text → 0 | Will not parse "42" as 42 | | TO_PURE_NUMBER | Formatted numbers/dates/percents → raw number | Input should already be numeric-typed | | DATEVALUE | Date strings → date | Locale-sensitive parsing | | TEXT | Number/date → display string | Result is text; math needs reconversion | | TO_TEXT | Any value → text | Less format control than TEXT | | TO_PERCENT | Fraction → percent-typed value | Pass 0.25, not 25, unless you intend 2500% |

Common mistakes

• Multiplying text-numbers in one row and assuming SUM agrees. SUM ignores text; fix the column with VALUE.
• Using N on CSV amounts. Text amounts become 0. Use VALUE.
• TEXT for storage, then surprise #VALUE! later. Format for humans in views; keep raw numbers in the model.
• Locale decimal marks. VALUE("1,5") may fail in a US locale sheet; normalize separators before coercing.
• Silent IFERROR(..., 0) on every row. Zeros can look like real amounts — prefer blank fallbacks and the discipline in Dealing with Errors in Google Sheets.

Going further

Clean strings first (TRIM, CLEAN, SUBSTITUTE), then convert types, then validate inputs (Data Validation in Google Sheets). Related reading: Data Cleaning, Sorting, and Analysis Tips in Google Sheets, Normalize Dates in Google Sheets with DATEVALUE and TEXT. Function pages: VALUE, N, TO_TEXT, TO_PERCENT, TO_PURE_NUMBER, DATEVALUE, TEXT.