CONVERT NUMBERS STORED AS TEXT TO NUMBERS

It’s comtháng khổng lồ find numbers stored as text in Excel. This leads to incorrect calculations when you use these cells in Excel functions such as SUM & AVERAGE (as these functions ignore cells that have sầu text values in it). In such cases, you need to lớn convert cells that contain numbers as text baông chồng tonumbers.

Bạn đang xem: Convert numbers stored as text to numbers

Now before we move forward, let’s first look at a few reasons why you may over up with a workbook that has numbers stored as text.

Using ‘ (apostrophe) before a number. A lot of people enter apostrophe before a number lớn make it text. Sometimes, it’s also the case when you tải về data from a database. While this makes the numbers show up without the apostrophe, it impacts the cell by forcing it to lớn treat the numbers as text. Getting numbers as a result of a formula (such as LEFT, RIGHT, or MID) If you extract the numerical part of a text string (or even a part of a number) using the TEXT functions, the result is a number in the text format.

Now, let’s see how to lớn tackle such cases.

Convert Text lớn Numbers in Excel

In this tutorial, you’ll learn how khổng lồ convert text to numbers in Excel.

The method you need khổng lồ use depends on how the number has been converted inlớn text. Here are the ones that are covered in this tutorial.

Using the ‘Convert to lớn Number’ option. Change the format from Text khổng lồ General/Number. Using Paste Special. Using Text khổng lồ Column. Using a Combination of VALUE, TRIM, and CLEAN function.

Convert Text lớn Numbers Using ‘Convert lớn Number’ Option

When an apostrophe is added lớn a number, it changes the number format khổng lồ text format. In such cases, you’ll notice that there is a green triangle at the top left part of the cell.

In this case, you can easily convert numbers to lớn text by following these steps:

Select all the cells that you want lớn convert from text lớn numbers. Cliông xã on the yellow kim cương shape icon that appears at the top right. From the menu that appears, select ‘Convert to Number’ option.

This would instantly convert all the numbers stored as text bachồng lớn numbers. You would notice that the numbers get aligned to the right after the conversion (while these were aligned lớn the left when stored as text).

Convert Text to lớn Numbersby Changing Cell Format

When the numbers are formatted as text, you can easily convert it baông xã to numbers by changing the format of the cells.

Here are the steps:

Select all the cells that you want to lớn convert from text to lớn numbers. Go lớn trang chủ –> Number. In the Number Format drop-down, select General.

This would instantly change the format of the selected cells to lớn General and the numbers would get aligned to the right. If you want, you can select any of the other formats (such as Number, Currency, Accounting)which will also lead to lớn the value in cells being considered as numbers.

Xem thêm: Cách Làm Cho Laptop Chạy Nhanh Hơn, 11 Cách Giúp Máy Tính Chạy Nhanh Hơn

Also read: How to Convert Serial Numbers to Dates in Excel

Convert Text to lớn Numbers Using Paste Special Option

To converttext to lớn numbers using Paste Special option:

Enter 1 in any empty cell in the worksheet. Make sure it is formatted as a number (i.e., aligned lớn the right of the cell). Copy the cell that contains 1. Select the cells that you want lớn convert from text lớn numbers. Right-click và select Paste Special. In the Paste Special dialog box, select Multiply within the Operation category. Cliông chồng OK.

Convert Text to Numbers Using Text lớn Column

This method is suitable in cases where you have sầu the data in a single column.

Here are the steps:

Select all the cells that you want lớn convert from text khổng lồ numbers. Go khổng lồ Data –> Data Tools –> Text to lớn Columns. In the Text to Column Wizard: In Step 1: Select Delimited and clichồng on Next. In Step 2: Select Tab as the delimiter and clichồng on Next. In Step 3: In Column data format, make sure General is selected. You can also specify the destination where you want the result. If you don’t specify anything, it will replace the original data phối.

While you may still find the resulting cells to be in the text format, & the numbers still aligned to the left, now it would work in functions such as SUM và AVERAGE.

Convert Text to lớn Numbers Usingthe VALUE Function

You can use a combination of VALUE, TRIM & CLEAN function toconvert text to lớn numbers.

VALUE function converts any text that represents a number baông xã khổng lồ a number. CLEAN function removes extra spaces & non-printing characters that might sneak in if you import the data or tải về from a database.

Suppose you want convert cell A1 from text lớn numbers, here is the formula:

=VALUE(TRIM(CLEAN(A1))) If you want to apply this to other cells as well, you can copy & use the formula.