Excel 與 Google Sheets 將文本數字轉換為數值：5 種方法

Tested in: Live formula checks in Microsoft Excel 16.114.4 for Mac (English UI; system region zh-Hans_US), November 8, 2026; Google Sheets (spreadsheet locale zh_CN), November 9, 2026. Results and platform differences are noted below; this is not a certification of every menu workflow.

Which versions this applies to

• Excel for Microsoft 365, 2024, 2021, 2019, 2016 (Windows): All five methods. NUMBERVALUE is listed for Excel 2016 and later on Microsoft’s page.
• Excel for Mac and Excel for the web: Methods 1, 2 and 4. Microsoft’s convert-to-number page covers Windows and web.
• Google Sheets: Method 4 (formulas) plus the Sheets section below. Sheets has no green-triangle warning.

How to tell a number is actually text

The difference between + and SUM explains a lot of confusion. In our LibreOffice check, =SUM(A2:A2) on a text “00123” returned 0, while =A2+0 returned 123: arithmetic operators convert text, while SUM skips text in a range. That’s why one total in your report can be right and another wrong.

Why it happens

• The column was formatted as Text before the data went in. Microsoft documents that if you format a cell as Text, type a number, and then change the format to Number, the cell stays text. Changing the format alone doesn’t convert anything.
• Leading apostrophe. '1003 forces text on purpose.
• Imports and exports. CSV files, ERP and banking exports, and copied web tables.
• Formulas that return text. LEFT, MID, RIGHT, TEXT, TRIM, SUBSTITUTE and & always return text, even when the result looks like a number. =LEFT("2026-INV",4) is the text “2026”.
• Hidden characters. A non-breaking space or a tab glued to the number. See TRIM not working (CHAR 160).

Example data

Put these values in A2:A10. Every one of them is text:

Method 1: Convert to Number (the green triangle)

1. Select the cells. You can select the whole column, and the menu works on all flagged cells at once.
2. Click the warning icon that appears next to the selection, or press Alt+Shift+F10, as Microsoft’s page suggests.
3. Choose Convert to Number.

If you don’t see triangles at all, error checking may be off. Turn it on under File > Options > Formulas > Enable background error checking and make sure the rule Numbers formatted as text or preceded by an apostrophe is ticked.

Best for: clean digits that Excel already flagged. Won’t help with: rows 7–9 above, which Excel doesn’t recognize as numbers in the first place.

Method 2: Paste Special > Multiply by 1

Microsoft describes this as multiplying each cell by 1 to force the conversion. The value looks the same, but it’s now a number:

1. Type 1 in an empty cell formatted as General and copy it (Ctrl+C).
2. Select the text numbers.
3. Home > Paste > Paste Special, choose Multiply, click OK.
4. Delete the helper 1.

This converts the cells in place, with no helper column. Choosing Add with an empty copied cell (adding 0) is a common variation.

Method 3: Text to Columns > Finish

Select a single column, then go to Data > Text to Columns and click Finish straight away. Excel re-enters every cell and converts anything that looks like a number. Microsoft’s #N/A troubleshooting page recommends this for mismatched number and text values.

Text to Columns has a less-known option for trailing minus signs (row 7). Click Next twice, then Advanced, tick Trailing minus for negative numbers, and also set the decimal and thousands separators to match your data (useful for row 8). Then click Finish.

Watch out: It works on one column at a time and overwrites the column to its right if you accidentally choose a delimiter.

Method 4: Formulas (VALUE, --, NUMBERVALUE)

A formula is the only method that can also clean the text on the way. Put it in a helper column, then copy and use Paste Special > Values over the original.

Results in LibreOffice 25.2 (en-US) for the example rows. VALUE, -- and *1 gave identical results on every row:

No single function handles everything. Use -- for clean digits, NUMBERVALUE for spaces and foreign separators, and a SUBSTITUTE for anything with hidden characters.

Trailing minus:

=IF(RIGHT(A7)="-",-LEFT(A7,LEN(A7)-1),--A7)

Non-breaking space inside a number: remove it (replace with nothing, not a space), then convert:

=--SUBSTITUTE(A9,UNICHAR(160),"")

Everything at once: strip spaces of both kinds, then convert. Errors become blank so you can spot rows that need a manual look:

=IFERROR(--SUBSTITUTE(SUBSTITUTE(TRIM(A2),UNICHAR(160),"")," ",""),"")

Don’t use N() for this. =N("123") returns 0, not 123. N passes numbers through and turns text into 0.

Empty cells: results vary. Microsoft documents that NUMBERVALUE("") returns 0. In LibreOffice, NUMBERVALUE of an empty cell returned #VALUE!, while VALUE and -- of an empty cell returned 0. Neither is what you want in a data column, so guard blanks with IF(A2="","",…) and empty rows stay empty.

Method 5: Power Query (for files you import every week)

If the same export arrives every week, fix the type once in Power Query: Data > From Table/Range, select the column, set Data Type to Decimal Number or Whole Number, and choose Close & Load. Next week, replace the source file and click Refresh. If some rows fail to convert, Power Query marks them as errors, so you can see exactly which rows are bad instead of having them silently ignored.

Google Sheets

Sheets doesn’t flag text numbers, so check with =ISTEXT(A2) or =COUNTA(A2:A)-COUNT(A2:A).

• Formula: =VALUE(A2). Google’s documentation says VALUE converts any date, time or number format Sheets understands, and shows an error when it can’t. =--A2 works too. For a whole column in one cell:

=ARRAYFORMULA(IF(A2:A="",,VALUE(SUBSTITUTE(A2:A,CHAR(160),""))))

Then copy the result and use Edit > Paste special > Values only (Ctrl+Shift+V) over the original.

• Format > Number > Plain text is the Sheets equivalent of Excel’s Text format. Numbers typed into those cells stay text. Switch the format back to Automatic before re-entering data. Formatting alone does not reliably convert existing text; re-enter or parse it and verify with ISNUMBER.
• European separators: Sheets’ VALUE uses the spreadsheet’s locale (File > Settings > Locale). For text from another locale, swap the separators with SUBSTITUTE first: =VALUE(SUBSTITUTE(SUBSTITUTE(A8,".",""),",",".")) in a US-locale sheet.
• Why it matters more in Sheets: QUERY uses the majority data type in each column and treats the minority as null, so a few text numbers can disappear from QUERY results. See FILTER vs QUERY.

Hard cases: when NOT to convert

• Codes with leading zeros (ZIP codes, employee IDs, SKUs like 00123). Converting turns 00123 into 123. Keep these as text, and convert the other side of a lookup to text instead (see XLOOKUP #N/A fixes). If you want the digits stored as numbers but displayed padded, use a custom format such as 00000.
• Numbers longer than 15 digits (card numbers, some tracking and IMEI numbers). Microsoft’s specifications list Excel’s number precision as 15 digits, so converting 1234567890123456789 to a number changes the trailing digits to zeros. These must stay text.
• Dates stored as text. VALUE converts them to serial numbers, but whether “03/04/2026” means March 4 or April 3 depends on your locale. Use the dedicated guide: Excel dates stored as text.

Common mistakes

1. Changing the number format and expecting a conversion. Microsoft confirms that the cell stays text. Re-enter the values with one of the five methods.
2. Clicking “Ignore Error”. It hides the triangle, not the problem.
3. Using N() to convert. It returns 0 for text.
4. Converting codes and long IDs. You lose leading zeros and digits after the 15th.
5. Leaving the helper formulas in place. Paste values back over the original, or every report has to point at the helper column.

FAQ

How do I convert text to numbers in Excel quickly?

Select the cells, click the green warning icon and choose Convert to Number. If there is no icon, use Data > Text to Columns > Finish on the column, or Paste Special > Multiply by 1.

What’s the difference between VALUE and double negative (—)?

Both convert text that looks like a number into a number, and both return #VALUE! for text they can’t read. VALUE is easier to read. — is shorter and common inside bigger formulas such as SUMPRODUCT.

Why does changing the format to Number not work?

The format only changes how a value is displayed. Microsoft documents that a cell formatted as Text keeps its text value after you switch to Number. Re-enter it with Convert to Number, Text to Columns or a formula.

How do I convert numbers with commas as decimal separators?

Use NUMBERVALUE and specify the separators: =NUMBERVALUE(A2,”,”,”.”) converts “1.234,50” to 1234.5 regardless of your Excel locale.

How do I convert text to numbers in Google Sheets?

Use =VALUE(A2) or =—A2 in a helper column, or =ARRAYFORMULA(IF(A2:A="",,VALUE(A2:A))) for the whole column. Then paste the results back with Paste special > Values only.

Related tutorials

• TRIM not working in Excel? Remove non-breaking spaces (CHAR 160)
• XLOOKUP returns #N/A even though the value exists: 7 fixes
• Excel dates stored as text: how to convert them
• VLOOKUP returning the wrong value
• FILTER vs QUERY in Google Sheets

Live-test findings

The table above preserves the labelled LibreOffice baseline; it is not an Excel result table. On Excel 16.114.4 for Mac, NUMBERVALUE returned 1234.5 for $1,234.50, -125 for (125), and #VALUE! for 125-. Data-menu entries for Text to Columns and Get Data (Power Query) were present, but Advanced separators and Using Locale were not tested end to end.

Sources

• Microsoft Support: Convert numbers stored as text to numbers in Excel (Convert to Number, Alt+Shift+F10, enable background error checking, VALUE + Paste Special Values): https://support.microsoft.com/en-us/excel/convert-numbers-stored-as-text-to-numbers-in-excel
• Microsoft Support: Fix text-formatted numbers by applying a number format (Paste Special multiply by 1, error-checking rule, TRIM/CLEAN for extra characters): https://support.microsoft.com/en-us/excel/fix-text-formatted-numbers-by-applying-a-number-format
• Microsoft Support: You cannot convert text to number when the cell contains a number (Text format stays after switching to Number): https://support.microsoft.com/en-us/topic/you-cannot-convert-text-to-number-when-the-cell-contains-a-number-1791c3e1-c2b0-4e5e-6bd8-b100f291e4f4
• Microsoft Support: VALUE function (rarely needed because Excel converts automatically; #VALUE! for unrecognized text): https://support.microsoft.com/en-us/excel/functions/value-function
• Microsoft Support: NUMBERVALUE function (custom separators, spaces ignored, "" returns 0, percent signs): https://support.microsoft.com/en-us/excel/functions/numbervalue-function
• Microsoft Support: How to correct a #N/A error (Text to Columns for mismatched number and text values): https://support.microsoft.com/en-us/excel/how-to-correct-a-n-a-error
• Microsoft Q&A: Move negative sign from end of string to beginning (Text to Columns > Advanced > Trailing minus): https://learn.microsoft.com/en-us/answers/questions/4762502/move-negative-sign-from-end-of-string-to-beginning
• Microsoft Support: Excel specifications and limits (number precision 15 digits): https://support.microsoft.com/en-us/excel/excel-specifications-and-limits
• Google Docs Editors Help: VALUE (converts formats Sheets understands; error otherwise): https://support.google.com/docs/answer/3094220
• Google Docs Editors Help: QUERY function (mixed data types: majority type wins): https://support.google.com/docs/answer/3093343
• LibreOffice Calc 25.2: local verification of VALUE, —, *1, NUMBERVALUE, N(), the trailing-minus and SUBSTITUTE formulas, and SUM vs + behavior.