How to convert percentage text to a number in Excel
Formula (Excel and Google Sheets)
=VALUE(A2)VALUE understands the percent sign: =VALUE(A2) turns the text "45%" into the number 0.45.
- Result on the example
- one result per row in
B2, filled down toB7 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.
How it works
The imported rates are text, so they can't be used in calculations. VALUE reads text the way Excel would read it typed into a cell, and a trailing % means "divide by 100": "45%" becomes 0.45. Format the results as percentages to show them as 45% again, now as real numbers.
=VALUE(A2)Result0.45
A2"45%"The value in A2.
VALUE(A2)0.45VALUE: Converts text that looks like a number into a number.- text
A2"45%"
- text
In Google Sheets
The same formula works in Google Sheets: =VALUE(A2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =--A2 | 0.450, 0.125, 0.070… | The double minus forces the same conversion; shorter, and common inside larger formulas. |
| =SUBSTITUTE(A2,"%","")/100 | 0.450, 0.125, 0.070… | Removes the % sign and divides by 100. Works when the text has extra spaces or a format VALUE rejects. |
Common mistakes
- Text with a comma as the decimal separator ("12,5%") converts only in locales that use a decimal comma. Use SUBSTITUTE to swap the comma first.
- If the column holds plain numbers like 45 that mean 45%, they are numbers already: divide by 100 instead.
- Formatting the text cells as Percentage doesn't convert them; they need a formula or Text to Columns.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Convert percentage text to a number
B2, convert the imported percentage text into a number, then fill down to B7.Questions
How do I convert a whole column in place?
Select it and use Data, Text to Columns, Finish. Excel converts each "45%" to 0.45.
Does this work in Google Sheets?
Yes, VALUE("45%") returns 0.45 there too.
How do I handle a column that mixes "45%" and "45"?
Divide only the ones without a sign: =IF(ISNUMBER(SEARCH("%",A2)),VALUE(A2),VALUE(A2)/100).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.