Pinterest Pixel

How to Convert Text to Numbers in Microsoft Excel

Excel is a great tool with numerous features that can streamline data management and analysis. But sometimes,... read more

John Michaloudis
Posted on

Overview

How to Convert Text to Numbers in Microsoft Excel | MyExcelOnline How to Convert Text to Numbers in Microsoft Excel | MyExcelOnline

How to Convert Text to Numbers in Microsoft Excel
Excel is a great tool with numerous features that can streamline data management and analysis. But sometimes, you may come across situations where numbers in your spreadsheet are mistakenly categorized as text, resulting in calculation errors and formatting problems. In this article, we will explore different ways to easily convert text to numbers:

Let’s look at these methods thoroughly!

Checking if a Value is Numeric or Text

When working with data in Excel, it’s important to establish if a value is numeric or text. This information allows you to apply appropriate calculations, formatting, and data analysis techniques.

A great place to start is by looking at where the value is aligned within the cell. In Excel, text values are always aligned to the left of a cell, while numeric values are aligned to the right. However, this method is not totally foolproof as users can change the alignment of the cell values.

How to Convert Text to Numbers in Microsoft Excel

Another way to check is by looking at the Status Bar. As you can see, the numeric values will display the average, count and sum calculations. Whereas if the cells are in text format, it will only display the count.

How to Convert Text to Numbers in Microsoft Excel How to Convert Text to Numbers in Microsoft Excel
In order for us to be 100% sure, we can use the ISNUMBER Function. The cell will display either TRUE if the value is numeric or FALSE if it is not.

See also  How to Use Substrings in Microsoft Excel

Select a cell where you want to display the result.

Enter the formula =ISNUMBER(A2) in the selected cell, assuming the value you want to check is in cell A2.

Press Enter to apply the formula.

How to Convert Text to Numbers in Microsoft Excel How to Convert Text to Numbers in Microsoft Excel
By using the ISNUMBER function, you can quickly determine the nature of a value in Excel and proceed with appropriate calculations and data handling techniques.

Now that we have determined if the Value is Numeric or Text, we can move on to How to Convert Text to Number.

Excel’s Built-in Convert to Number Feature

If you encounter cells displaying an error indicator (such as a green triangle in the top-left corner), converting text strings to numbers is a simple two-step process:

Select all the cells that contain numbers formatted as text.

Click on the warning sign displayed and choose the option Convert to Number.

How to Convert Text to Numbers in Microsoft Excel

That’s it! The text strings will be converted into numeric values, resolving any formatting issues or calculation errors.

Change the Format

Follow these steps to convert text to numbers using the Change the Format method:

Select the range of cells containing the text-formatted numbers that you want to convert.

Right-click on the selected range and choose Format Cells from the context menu. Alternatively, you can go to the Home tab in the Excel ribbon, click on the Number Format drop-down arrow, and select Format Cells.

See also  How to Create a Histogram in Excel: A Step-by-Step Guide with Examples

In the Format Cells dialog box, go to the Number tab.

Choose the desired category for your numbers (e.g., “Number,” “Currency,” “Percentage”).

Specify the number of decimal places and other formatting options as needed.

How to Convert Text to Numbers in Microsoft Excel

Click OK to apply the new format to the selected cells.

Excel will automatically convert the text-formatted numbers into actual numeric values with the specified format. This method allows you to control the formatting style of the resulting numeric values.

Using the Paste Special

If for some reason you cannot see the green triangle on the upper left-corner of the cell and it is still showing as a text, you can follow these steps to change text to numbers using the Paste Special feature in Excel:

Enter the number 1 in an empty cell and copy it (Ctrl+C).

Select the cell(s) containing the text-formatted numbers that you want to convert.

Go up to the Home ribbon and click on the Paste drop-down arrow.

Select Paste Special.

How to Convert Text to Numbers in Microsoft Excel

Select Multiply and click OK.

How to Convert Text to Numbers in Microsoft Excel

The text-formatted numbers will now be converted to actual numeric values with the specified format.

See also  2 Best Ways to Use the IF ELSE Function in Excel (Examples)

Using Text to Columns

Follow these steps to convert text to numbers using the Text to Columns feature in Excel:

Select the range of cells containing the text-formatted numbers that you want to convert.

Go to the Data tab in the Excel ribbon.

Click on the Text to Columns button in the Data Tools group. The Convert Text to Columns Wizard will open.

In the wizard, choose the option Delimited and click Next.

How to Convert Text to Numbers in Microsoft Excel

Select the delimiter that separates your text values (e.g., space, comma, tab). For our case, deselect all checkboxes. Click Next.

How to Convert Text to Numbers in Microsoft Excel

In the Column data format section, select the General option.

How to Convert Text to Numbers in Microsoft Excel

Click Finish to apply the changes.

Excel will now convert the text-formatted numbers to actual numeric values using the Text to Columns feature.

Remember to make a backup of your data before applying any changes to ensure data integrity.

Convert Text to Number Using Formula

The previous methods are very useful for quick fixes. But in order to fully automate the solution, a formula may be needed. With this method, we will not change the values in the cells containing the text. Rather, we will create a numbers version in another column.

Use this formula: =RIGHT(A2,LEN(A2)-1)

How to Convert Text to Numbers in Microsoft ExcelThe extraction of the number was achieved successfully using a formula. But the RIGHT and LEN are text functions, causing the numbers to be returned in text format.

See also  How to Insert Dotted Lines in PowerPoint Effectively

We can use the VALUE function to transform text representations of numbers into numerical values.

=VALUE(RIGHT(A2,LEN(A2)-1))

How to Convert Text to Numbers in Microsoft Excel

There you have it! Being able to determine whether a value in Excel is numeric or text is important for accurate data manipulation and analysis. Be sure to use these methods into your Excel workflows next time!

If you like this Excel tip, please share it
How to Convert Text to Numbers in Microsoft Excel | MyExcelOnline How to Convert Text to Numbers in Microsoft Excel | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...