# How to convert percentage text to a number in Excel

VALUE understands the percent sign: `=VALUE(A2)` turns the text "45%" into the number 0.45.

**Excel:** `=VALUE(A2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Imported rate | Number |
| 45% |  |
| 12.5% |  |
| 7% |  |
| 100% |  |
| 0.5% |  |
| -3% |  |

Formula in B2, filled to B7: `=VALUE(A2)`

Result: 0.450; 0.125; 0.070; 1.000; 0.005; -0.030

## 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.

## Other ways

- `=--A2`: The double minus forces the same conversion; shorter, and common inside larger formulas.
- `=SUBSTITUTE(A2,"%","")/100`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/convert-percentage-text-to-number