How to Convert Text Like "$1 Billion" to a Number in Excel
Question details
The user needs to convert text representations of currency that include scale words (e.g., "$1 billion") into usable numerical values (e.g., 1000000000) so they can be used in mathematical calculations.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing or copying financial data containing text-based scale words (Million, Billion) that need to be used in data analysis and mathematical formulas.
- Observed behavior
- Excel recognizes values like "$1 billion" as text strings rather than numbers, which prevents any sum, average, or other mathematical operations from working properly.
Before applying formulas, quickly scan your dataset to identify all the different scale words (such as Million, Billion, or Trillion) and currency symbols used, ensuring you account for all variations.
Use an Automated Formula for Mixed Scale Words
A robust nested formula can automatically detect words like "Million" or "Billion", strip the currency symbol, and multiply by the correct numerical value.
This method is highly recommended if your dataset contains a mix of different scale words. It uses the SEARCH function to identify the word, SUBSTITUTE to remove unwanted text, and VALUE to convert the remaining string into a calculable number.
Assuming your text value is in cell A2, click on cell B2 (or any adjacent empty cell) to begin entering your formula.
Type the following formula: =IF(ISNUMBER(SEARCH("billion",A2)), VALUE(SUBSTITUTE(SUBSTITUTE(LOWER(A2),"$","")," billion",""))*1000000000, IF(ISNUMBER(SEARCH("million",A2)), VALUE(SUBSTITUTE(SUBSTITUTE(LOWER(A2),"$","")," million",""))*1000000, VALUE(SUBSTITUTE(A2,"$",""))))
Press Enter to execute the formula. Then, click the small square at the bottom-right corner of cell B2 and drag it down to apply the formula to the rest of your data.
Select your newly generated numerical data, right-click, choose "Format Cells", and select "Currency" or "Number" to display the values properly.
Use Find and Replace for Uniform Data
If your dataset contains only one type of scale word (e.g., everything is in billions), you can use the Find and Replace tool to remove the text and multiply by the scale factor.
Convert Text to Numbers Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers identical formula functions and data manipulation tools to Excel, allowing you to convert text strings into numbers effortlessly while being completely free and lightweight.
- 1. Open your dataset in WPS: Launch WPS Office and open the spreadsheet workbook containing your text-formatted currency values.
- 2. Apply the conversion formula: Select an empty cell next to your data and enter the nested IF and SUBSTITUTE formula to strip the text and multiply by the scale.
- 3. Fill and format: Drag the fill handle to apply the formula to all rows, then apply the Currency number format from the Home tab to finish.
Frequently Asked Questions
Why does Excel treat "$1 billion" as text instead of a number?
Excel recognizes numeric values based on specific built-in formats. Since "billion" is a word and not a standard numerical scale indicator in Excel's default formatting, the entire cell is treated as a text string, preventing mathematical calculations.
Can I use Flash Fill to convert text to numbers?
Flash Fill can sometimes recognize patterns if you manually type the desired number (e.g., 1000000000) in the adjacent cell and press Ctrl + E. However, it may struggle with varying scales (mixing millions and billions) or complex decimals, making a formula a more reliable approach.
How do I deal with decimal values like "$1.5 billion"?
The formula method using SUBSTITUTE and VALUE naturally handles decimals. When "$1.5 billion" is stripped of its text and multiplied by 1,000,000,000, Excel will correctly calculate the result as 1500000000.
What if my data has varying currency symbols?
If your dataset contains Euros, Pounds, or other symbols, you can nest additional SUBSTITUTE functions in your formula to strip those specific characters out, or use the MID function combined with FIND to extract just the numeric portion of the text.
