How to Convert Text to Number in Excel

By default, numerals are oriented to the right and text to the left. You may learn how to convert text strings that represent numbers to numbers with this example. 

1. Convert Text to Number with Error Checking Function

In this method, we will explore how to convert text to numbers in Excel using the Error Checking function. This function allows you to easily identify and convert text-formatted cells into number format, ensuring accurate calculations and data manipulation.

Step 1. Select the cells that you want to convert to number format.

Step 2. When you select these cells, a yellow diamond icon with an exclamation point will appear near your selection.

Step 3. Click on this icon to open the error checking menu.

Step 4. From the menu, choose "Convert to Number."

By following these steps, Excel will automatically convert the selected text-formatted cells into number format. This method is convenient for quickly converting multiple cells to numbers without the need for formulas or additional functions.

2. Convert Text to Number with Text to Column Function

In this method, we will explore how to convert text to numbers in Excel using the Text to Columns function. This function allows you to split and convert text data into separate columns, with the option to specify the desired data format.

Step 1. From the menu, navigate to the Data tab in Excel.

Step 2. In the Data Tools area of the ribbon, click on "Text to Columns."

Step 3. This action opens the Text to Columns Wizard window, which guides you through the conversion process.

Step 4. In the Wizard window, ensure that the "Delimited" option is selected, as it is commonly used for text conversions. Click Next to proceed.

Step 5. On the following Wizard screen, keep the default option selected (usually "Tab") and click Next again.

Step 6. On the final page of the Wizard, make sure "General" is chosen under the "Column data format" section. This format treats the converted data as numbers.

Step 7. Optionally, you can specify a new column in the Destination box to place the converted numerical data. Alternatively, you can leave the existing column as is. Click Finish to complete the process.

By following these steps, Excel will analyze the selected text column and split it into separate columns based on the chosen delimiter. The text will be converted to numbers according to the "General" format, enabling you to perform calculations and utilize numerical functions effectively.

3. Convert Text to Number by Modifying Cell Format

In this method, we will explore how to convert text to numbers in Excel by modifying the cell format. By adjusting the format of the selected cells, we can instruct Excel to treat the text as numeric data, allowing for mathematical operations and precise calculations.

To convert text to numbers using this method, follow these steps:

Step 1. Select the cells you want to transform. If you want to convert every cell in a column, you can choose the entire column except for the header.

Step 2. Go to the Home menu in the Excel ribbon.

Step 3. In the Number group, click on the Text dropdown box. This will display a list of available number formats.

Step 4. To convert the selected cells to number format, choose "General" from the list. This will treat the text as numbers.

Step 5. Alternatively, if you want to apply a specific number format to your numerical data, you can choose options like "Number," "Currency," "Accounting," or "Percentage" from the dropdown.

By following these steps, you can modify the cell format in Excel to convert text to numbers. Changing the format to a number format allows you to perform mathematical operations and utilize various numeric functions accurately on the converted text.

Trustpilot4.8

WPS Office- Free All-in-One Office Suite

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

5,820,008 User

Algirdas Jasaitis

4. Convert text to number by Paste Values Use

In this method, we will explore how to convert text to numbers in Excel using the "Paste Values" feature. This method allows you to convert text-formatted numbers to actual numeric values while discarding any associated formatting.

To convert text to numbers using the "Paste Values" method, follow these steps:

Step 1. Select a collection of blank cells where you want to generate your numerical data. Right-click and select "Format Cells" from the pop-up menu.

Step 2. In the Format Cells dialog box, make sure the "General" number format is chosen. Click OK to apply the format.

Step 3. Right-click on the entire column of cells that you want to convert from text to numbers. Choose "Copy" from the context menu.

Step 4. After formatting an empty column, select the first cell in that column. Right-click the cell and choose "Paste Values." This action pastes the values of the text-formatted numbers, using the general number format.

By following these steps, you can convert text to numbers using the "Paste Values" method. This method involves formatting an empty column with the desired number format and then pasting the values of the text-formatted numbers into that column.

5. Convert Text to Number with VALUE Function

In this section, we will explore the first method to convert text to numbers in Excel, which involves using the VALUE function. This function allows you to transform text strings into numeric values, enabling you to perform mathematical operations and utilize various Excel functions with ease.

Step 1. Click on an empty cell where you want to display the converted number.

Step 2. Go to the Formulas tab in the Excel ribbon and click on the "Insert Function" button to open the function dialog.

Step 3. In the search box of the dialog, type "VALUE" and select the "VALUE" function from the list of suggestions. Click OK.

Step 4. In the new dialog that appears, select the cell containing the text you want to convert to a number. Click OK.

Step 5. The VALUE function will now return the numeric value of the text string in the selected cell.

Step 6. To convert the rest of the cells, you can simply drag the fill handle (the small square in the bottom-right corner of the selected cell) down or across the desired range of cells. Excel will automatically apply the VALUE function to each cell, converting the text to numbers.

By following these steps, you can effectively convert text to numbers in Excel using the VALUE function.

FAQs About Convert to Number in Excel

Q1: What is the shortcut for converting to number in Excel?

The shortcut for converting text to numbers in Excel is Alt + D, E, F.

Q2: Is there a formula to CONVERT text to number in Excel?

Yes, there is a formula to convert text to numbers in Excel. You can use the VALUE function, like this: =VALUE(text).

Q3: How do I remove a formula and keep a value in Excel?

Yes, there is a formula to convert text to numbers in Excel. You can use the VALUE function, like this: =VALUE(text).

Summary

Did you learn about how to convert text to number in an  Excel file? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.