Converting text to numbers in Excel is one of the most common data cleanup tasks. When imported data has numbers stored as text, calculations don't work, sorting is wrong, and aggregations fail. Excel offers multiple methods to fix this — from simple multiply-by-1 to dedicated VALUE function to Paste Special tricks.
Why this matters. Numbers stored as text look like numbers but Excel treats them as text. Math operations fail. SUM returns 0. Sorting alphabetizes (1, 10, 11, 2, 3) instead of sorting numerically. Critical to fix before analysis.
How to identify text-stored numbers. Text aligns LEFT in cells (numbers align RIGHT). Green triangle in upper-left corner (Excel's error indicator). Apostrophe before number when you click cell. Math operations on cell don't work as expected.
Common causes. Data import (CSV, web scraping). Copy-paste from external sources. Leading apostrophes from previous data entry. Wrong cell format applied.
This guide covers 7 reliable methods to convert text to numbers in Excel, with examples and tips for different scenarios. By the end, you'll be able to handle any text-to-number conversion challenge.
Method 1: VALUE function. The explicit approach.
Syntax. =VALUE(text). Returns numeric value of text. Standard Excel function.
Usage. In B1: =VALUE(A1). If A1 contains text-stored '123', B1 returns numeric 123.
Why use VALUE. Explicit conversion. Easy to read in formula bar. Clear intent. Works on individual cells or in formulas.
Limitations. Returns #VALUE! if cell is truly text (not text-stored number). Won't convert '$1,234' (with formatting). Won't convert '12,345' (with comma).
For formatted text. Use VALUE with cleaning: =VALUE(SUBSTITUTE(A1, ',', '')) removes commas. =VALUE(SUBSTITUTE(A1, '$', '')) removes dollar signs. =VALUE(SUBSTITUTE(A1, '%', '')/100) for percentages.
For multiple cells. Copy formula down. Each row converts. Then copy results, paste-special as values to replace original text.
When to use. Single cell or column conversion. Clear formula approach. Easy to explain.
Method 2: Multiply by 1. The simplest conversion.
Usage. =A1*1. Returns numeric if A1 contains text-stored number.
Why it works. Excel converts text to number when math operation applied (where possible). Multiplying by 1 doesn't change value but forces conversion.
Variations. =A1+0 (add 0, same effect). =A1/1 (divide by 1). =--A1 (double negative, forces conversion).
When to use. Quick inline conversion. Don't need to remember VALUE function. Standard math operator approach.
Limitations. Same as VALUE — won't handle formatted text without additional cleaning.
=VALUE(A1). Explicit. Clear intent. Best for documentation.
=A1*1. Simplest. Math operator forces conversion.
=A1+0. Same effect. Common alternative.
=A1/1. Works but unconventional.
=--A1. Forces conversion. Concise.
=VALUE(SUBSTITUTE(A1, ',', '')). Removes commas first.
Method 3: Paste Special Multiply. Fastest for bulk conversion.
Setup. Find empty cell. Type 1. Copy that cell (Ctrl+C). Select range of text-stored numbers.
Apply. Paste Special (Ctrl+Alt+V). Choose 'Multiply' under Operation. Click OK. Excel multiplies all selected cells by 1, converting text to numbers in place.
Why this works. Math operation forces conversion. Done on all selected cells simultaneously. Most efficient for bulk conversion.
Advantages. Fast. No formula columns. Done in place. Works on entire column at once.
Disadvantages. Modifies original cells permanently. Save backup first if data is sensitive.
When to use. Multiple cells to convert. Don't need to preserve original text values. Want fastest method.
Method 4: Text to Columns. Comprehensive wizard.
Setup. Select column with text-stored numbers. Data tab → Text to Columns.
Wizard. Choose Delimited or Fixed Width (either works for single column). Click Next. Click Next again. On Step 3, choose Column data format → General.
Apply. Click Finish. Excel re-evaluates each cell. Converts text-stored numbers to numbers.
Why this works. Text to Columns is Excel's data type re-evaluation tool. Forces type conversion. Handles many edge cases.
Advantages. Most thorough. Handles trailing spaces, hidden characters. Works on whole column at once.
Disadvantages. Multiple clicks. Modifies original column. Save backup first.
When to use. Stubborn text data. When other methods fail. Whole columns of mixed text/numbers.
Fastest bulk method. Type 1 in cell, copy it. Select range. Paste Special → Multiply. All cells convert in place. Cells modified — save backup first.
Excel wizard. Data tab → Text to Columns. Delimited → Next → Next → General → Finish. Forces type conversion. Most thorough. Handles edge cases.
Click yellow triangle in cell upper-left corner. Choose 'Convert to Number.' Works on single cell or selected range. Quick visual approach.
Ctrl+H. Find apostrophe ('), Replace with nothing. Removes leading apostrophes that prevent number conversion.
Create formula column. =VALUE(A2). Copy down. Then copy formula column. Paste Special → Values to original column. Replaces text with numbers.
Get & Transform data. Right-click column → Change Type → Number. Powerful for recurring data imports. Saves transformation steps.
Method 5: Yellow error indicator. The visual approach.
What it looks like. Yellow triangle (warning) in upper-left of cell with text-stored number. Excel's built-in detection.
How to use. Click cell with indicator. Click small button that appears next to triangle. Choose 'Convert to Number.' Cell converts in place.
For multiple cells. Select range with indicators. Click button on first cell. Choose 'Convert to Number.' Excel converts all selected cells with same issue.
Why use this. Excel's built-in detection. Visual cue. Works on detected text-stored numbers.
Limitations. Only works on cells Excel recognizes as text-stored numbers. Cells with formatting issues may not show indicator.
When to use. Excel already showing yellow triangles. Quick visual fix.
Method 6: Find & Replace for apostrophes. Stripping leading apostrophes.
Common cause of text-stored numbers. Apostrophe ' before number. Forces text storage. Easy to fix.
How to remove. Select cells with apostrophe-prefixed numbers. Ctrl+H to open Find & Replace. Find what: ' (apostrophe). Replace with: (leave empty). Click Replace All.
Result. Apostrophes removed. Cells convert to numbers automatically.
When to use. Specifically apostrophe-prefixed numbers. Quick fix for this specific cause.
Method 7: Power Query for repeatable processes.
When to use. Recurring data imports. Want automated conversion. Want to preserve original data.
Setup. Get & Transform Data (Excel 2016+). Import file. In Power Query, right-click column header → Change Type → Number.
Save query. Save the transformation. Refresh anytime to re-apply.
Why use this. Automated workflow. Preserves original file. Applies same conversion to refreshed data. Best for recurring imports.
Disadvantages. More complex than other methods. Overkill for one-time conversion.
Yellow triangle. Click button. 'Convert to Number.'
Remove apostrophes. Ctrl+H, find ', replace with nothing.
Recurring imports. Save transformation. Automated.
Manual entry. Slow but works for individual cells.
Custom code. =Value() function in macro. For automation.
Change cell format to General + F2 + Enter. Force re-evaluation.
Handling formatted text. When text contains symbols.
Numbers with $. '$1,234.56'. VALUE alone won't work. =VALUE(SUBSTITUTE(A1, '$', '')) removes dollar sign first.
Numbers with commas. '12,345'. =VALUE(SUBSTITUTE(A1, ',', '')). Removes thousand separators.
Numbers with both. '$12,345.67'. Chain SUBSTITUTE: =VALUE(SUBSTITUTE(SUBSTITUTE(A1, '$', ''), ',', '')).
Percentages. '50%'. =VALUE(SUBSTITUTE(A1, '%', ''))/100. Converts and adjusts for percent.
European number format. '1.234,56' (dot for thousands, comma for decimal). =VALUE(SUBSTITUTE(SUBSTITUTE(A1, '.', ''), ',', '.')). Replaces dots with nothing, comma with dot.
Numbers with units. '50 lbs'. =VALUE(LEFT(A1, FIND(' ', A1)-1)). Extracts number before space.
Numbers with prefixes. 'Order #12345'. =VALUE(MID(A1, FIND('#', A1)+1, 100)). Extracts after #.
Numbers with trailing characters. '12,345*'. =VALUE(LEFT(A1, LEN(A1)-1)). Strips last character.
Negative numbers in parentheses. '(1,234)'. =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, '(', '-'), ')', ''), ',', '')). Multiple substitutions.
Scientific notation. '1.5E3'. =VALUE(A1) handles this directly.
Dates as text. '1/2/2024'. =DATEVALUE(A1) for date, =VALUE(A1) for serial number.
'$1,234'. =VALUE(SUBSTITUTE(SUBSTITUTE(A1, '$', ''), ',', '')). Removes $ and commas before converting.
'12,345'. =VALUE(SUBSTITUTE(A1, ',', '')). Removes thousand separators.
'50%'. =VALUE(SUBSTITUTE(A1, '%', ''))/100. Converts to 0.5 decimal.
'(1,234)'. Multiple SUBSTITUTE to replace parens with minus, remove commas. Becomes -1234.
'1.234,56'. Swap dot/comma positions. =VALUE(SUBSTITUTE(SUBSTITUTE(A1, '.', ''), ',', '.')).
'50 lbs'. =VALUE(LEFT(A1, FIND(' ', A1)-1)). Extract number before space.
Troubleshooting common issues.
Conversion doesn't work. Cell appears unchanged. Possible causes: hidden characters, trailing spaces, special characters. Solution: =TRIM(A1) first, then convert. Or =CLEAN(A1) to remove non-printable characters.
VALUE returns #VALUE! error. Text contains non-numeric characters. Solution: SUBSTITUTE to remove them first. Or extract numeric portion with regex-like LEFT/MID/RIGHT.
Numbers showing as text in pivot table. Pivot doesn't recognize as numeric. Solution: convert source data first, then refresh pivot.
Sorted wrong. Numbers sorted as text (1, 10, 11, 2, 3 instead of 1, 2, 3, 10, 11). Solution: convert to numbers before sorting.
SUM returns 0. Cells stored as text. Solution: convert to numbers first. Or use SUMPRODUCT(VALUE(range)) to force conversion in formula.
VLOOKUP not finding. Number in source as text but lookup value as number (or vice versa). Solution: match data types. Convert both sides to same type.
Excessive decimal places. Conversion result has too many decimals. Solution: =ROUND(VALUE(A1), 2) to limit decimals.
Different results in different rows. Some cells convert, others don't. Solution: cells may have hidden characters. TRIM + CLEAN before conversion.
Conversion changes during file open. Excel auto-converting. Solution: ensure cells formatted correctly. Or use Text format for cells that shouldn't auto-convert.
Macro stops working. VBA expected number, got text. Solution: explicit conversion in VBA: CDbl(text) or Val(text).
Negative numbers wrong. Negative shown as text becomes positive. Solution: handle negative formatting carefully. Parentheses ((123)) or minus prefix (-123) need different conversion.
Best practices for text-to-number conversion.
Always backup first. Especially for bulk conversions. Save copy of original. Test on subset before full conversion.
Test on small sample. Convert 10 cells first. Verify result. Then proceed to full data.
Use formulas before in-place. Build formula column. Verify output. Then replace original with formula results via Paste Special.
Clean data first. TRIM whitespace. CLEAN non-printable characters. Standardize formatting. Then convert.
Choose method by scenario. Single cell: VALUE function. Many cells: Paste Special Multiply or Text to Columns. Recurring: Power Query. Visual fix: error indicator.
Document complex conversions. SUBSTITUTE chains can get complex. Comment formulas. Add notes for future review.
Verify after conversion. Spot-check converted cells. Compare to original (if you backed up). Verify aggregations work.
Use Excel Tables. Convert range to Excel Table. Better data type handling. Easier filtering of converted vs original.
Handle errors gracefully. =IFERROR(VALUE(A1), 0). Returns 0 instead of #VALUE! for unconvertible cells.
Pre-clean before VLOOKUP. Convert lookup column and lookup table to same type before joining.
For shared workbooks. Document conversion process. Other users may add new data that needs same conversion.
Build conversion templates. For recurring data imports, create Excel template with conversion formulas. Reuse for each new file.
Save copy before bulk conversions.
10 cells before full data.
Remove whitespace and hidden chars first.
Single cell: VALUE. Bulk: Paste Special. Recurring: Power Query.
Spot-check. Compare to original. Test aggregations.
Comment complex SUBSTITUTE chains. Aids future review.
Real-world scenarios and solutions.
Scenario 1: CSV import with currency values. Sales report imported with '$1,234' format. SUM returns 0. Solution: Text to Columns on column → General. Or formula column: =VALUE(SUBSTITUTE(SUBSTITUTE(A1, '$', ''), ',', '')).
Scenario 2: Database export with leading apostrophes. SQL export added apostrophes. SUM works incorrectly. Solution: Find & Replace ' with nothing. Or Paste Special Multiply.
Scenario 3: Web-scraped numbers with units. '50 lbs', '25 kg'. Need numeric only. Solution: =VALUE(LEFT(A1, FIND(' ', A1)-1)). Extracts number before space.
Scenario 4: Phone numbers stored as text. '(555) 123-4567'. Need numeric. Solution: =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, '(', ''), ') ', ''), '-', '')).
Scenario 5: Dates as text. '2024-01-15'. =DATEVALUE(A1) returns date serial number. =TEXT(A1, 'yyyy-mm-dd') reverses.
Scenario 6: Mixed numeric and text data. Some rows numbers, some text. Solution: =IF(ISNUMBER(A1), A1, VALUE(A1)). Or IFERROR for graceful handling.
Scenario 7: Percentages in text. '5.5%'. Solution: =VALUE(SUBSTITUTE(A1, '%', ''))/100. Or just /100 if number is already pre-multiplied.
Scenario 8: Financial reports with parentheses. '(1,234)' for negative. =VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, '(', '-'), ')', ''), ',', '')).
Scenario 9: European number format. '1.234,56'. =VALUE(SUBSTITUTE(SUBSTITUTE(A1, '.', ''), ',', '.')).
Scenario 10: Scientific notation as text. '1.5E3'. =VALUE(A1) handles directly. Excel parses scientific notation.
Scenario 11: Very large numbers. Numbers stored as text to preserve precision (over 15 digits). May actually want to keep as text. Don't convert if precision matters.
Currency, comma-separated values. Text to Columns or VALUE(SUBSTITUTE). Strip $ and commas first.
Apostrophe-prefixed numbers. Find & Replace ' with nothing. Or Paste Special Multiply.
Numbers with units (50 lbs). Extract numeric portion. =VALUE(LEFT(A1, FIND(' ', A1)-1)).
Formatted (555) 123-4567. Multiple SUBSTITUTE to remove non-numeric characters. Then VALUE.
'2024-01-15'. =DATEVALUE(A1) returns date serial. Treat as date, not regular number.
'(1,234)'. Replace ( with -, ) with nothing, remove commas. Becomes -1234.
Common questions about text-to-number conversion.
How do I convert text to number in Excel? Many ways: =VALUE(A1) function, =A1*1 math operation, Paste Special Multiply for bulk, Text to Columns wizard, or click yellow error triangle if Excel shows one.
Why does Excel show numbers as text? Numbers can be stored as text due to: data import (CSV), leading apostrophe before number, wrong cell format applied (Text), or copy from external sources.
How do I identify text-stored numbers? Text aligns LEFT in cells (numbers align RIGHT). Yellow error triangle in upper-left corner. Apostrophe before number when cell selected. Math operations return wrong results.
What's the fastest way to convert many cells? Paste Special Multiply. Type 1 in empty cell. Copy. Select range. Paste Special → Multiply. All cells convert in place instantly.
How do I handle numbers with currency symbols? Use VALUE with SUBSTITUTE: =VALUE(SUBSTITUTE(SUBSTITUTE(A1, '$', ''), ',', '')). Removes $ and commas first, then converts.
What's the difference between VALUE and multiply by 1? Same result for simple text-stored numbers. VALUE is explicit and clearer. Multiply by 1 is shorter syntax. Pick what suits your preference.
Why doesn't VALUE work on '$1,234'? VALUE only converts plain numeric text. Formatted text needs cleaning first via SUBSTITUTE to remove symbols.
How do I convert dates from text? Use DATEVALUE instead of VALUE. =DATEVALUE(A1) for dates. Date is a number but Excel treats specially.
What if conversion doesn't work? Check for hidden characters. TRIM and CLEAN first. Then convert. Some data has invisible characters preventing conversion.
Can I undo a Paste Special conversion? Yes, immediately with Ctrl+Z. Once saved, deleted original. Backup first if data is sensitive.
Final thoughts. Converting text to numbers in Excel is one of the most essential data cleanup skills. Whether dealing with imported CSVs, web-scraped data, accidentally text-formatted cells, or database exports, the methods covered here handle every scenario you'll encounter in real-world spreadsheet work.
Start with the basics. VALUE function is clearest and most explicit — easy to read in formula bar and self-documenting. Multiply by 1 is shortest and uses standard math operators. Paste Special Multiply is fastest for bulk conversion of many cells at once. Each has its specific place in your toolkit.
Handle formatted text. SUBSTITUTE removes symbols (currency, commas, percent signs) before VALUE. Chain SUBSTITUTE for multiple symbols. Build conversion formulas that handle your specific data format.
For bulk conversion. Paste Special Multiply is the fastest method. Type 1, copy, select range, Paste Special → Multiply. Done. Modifies original cells — backup first.
For recurring data. Power Query is the long-term solution. Define transformation once. Apply to refreshed data automatically. Best for monthly imports or repeated data feeds.
Watch for edge cases. Hidden characters, trailing spaces, mixed data types, lookup mismatches. Each requires specific handling. Test thoroughly.
Document complex conversions. SUBSTITUTE chains can get complex. Comment formulas. Add notes for future maintenance. Your future self will thank you.
Build conversion templates. For recurring import tasks, save Excel files with established conversion formulas. Reuse for new data. Saves time long-term.
Text-to-number conversion is foundational Excel work. Master it once, save hours of frustration with imported data forever. The investment in learning these methods pays back across every Excel project you'll work on. Worth the time.