Excel: convert text to number with formula and other ways

This Excel tutorial demonstrates various methods for converting text strings to numbers, addressing common spreadsheet issues where numbers formatted as text hinder calculations. The tutorial covers identification of the problem, and multiple solutions.

Sometimes, cells in your Excel worksheets display numbers but behave like text, causing formula errors. This often happens when numbers are formatted as text, even if Excel automatically converts some imported numerical strings. This tutorial details several ways to convert these "text numbers" into true numerical values.

• Identifying Numbers Formatted as Text
• Converting Text to Numbers:
  • Using Excel's "Convert to Number" Feature
  • Changing Cell Format
  • Using "Paste Special"
  • Using "Text to Columns"
  • Using Formulas (VALUE, DATEVALUE)
  • Using Mathematical Operations

Identifying Numbers Formatted as Text

Excel's error checking highlights potential issues with cell values using a green triangle. Hovering over this triangle reveals a message indicating text-formatted numbers. However, this indicator isn't always present. Other visual clues include:

Numbers	Text-Formatted Numbers
Right-aligned Status bar shows Average, Count, SUM (for multiple selections)	Left-aligned Status bar shows only Count (for multiple selections) Number Format shows "Text" (often, but not always) Leading apostrophe may be visible in the formula bar

The image below illustrates the difference:

Converting Text to Numbers

Several methods exist for converting text strings to numbers in Excel. The simplest are described first, with more advanced techniques for situations where simpler methods fail.

1. Using Excel's "Convert to Number" Feature:

If a cell displays the green error triangle, select the affected cells, click the warning sign, and choose "Convert to Number."

2. Changing Cell Format:

Select the cells, go to the "Home" tab, and in the "Number" group, choose "General" or "Number" from the dropdown. Note: This may not work in all cases (e.g., if the "Text" format was explicitly applied).

3. Using "Paste Special":

1. Set cell format to "General."
2. Copy a blank cell.
3. Select the text-number cells, right-click, and choose "Paste Special." (Or use Ctrl Alt V).
4. Select "Values" and "Add" in the dialog box. Click "OK."

4. Using "Text to Columns":

1. Select cells, set format to "General."
2. Go to the "Data" tab, click "Text to Columns."
3. In Step 1, choose "Delimited" and click "Finish."

5. Using Formulas:

• VALUE Function: Converts text strings to numbers, even those with currency symbols or separators. =VALUE("1,000") or =VALUE(A2).

• DATEVALUE Function: Converts text strings representing dates to Excel date serial numbers. =DATEVALUE("1-Jan-2018") or =DATEVALUE(A2).

• Extracting Numbers from Strings: Combine VALUE with text functions like LEFT, RIGHT, MID to extract numbers from complex strings. =VALUE(RIGHT(A2,3))

6. Using Mathematical Operations:

Adding zero, multiplying or dividing by one can force Excel to treat text as a number. =A2 0, =A2*1, =A2/1. Remember to set the cell format to "General" afterwards.

Use "Paste Special" to convert formulas to values if needed. Choose the method best suited to your needs.

The above is the detailed content of Excel: convert text to number with formula and other ways. For more information, please follow other related articles on the PHP Chinese website!