Excel: How to convert text to numbers

Excel: How to convert text to numbers

By default, texts are aligned to the left of your excel spreadsheet while numbers align to the right. While working with numbers, you may see some texts within your number fields. You will mistakenly change your numbers to text if you type apostrophe just before the number. This can also occur when you insert downloaded formulas into your spreadsheet. Having text in number fields is erroneous, and you have to fix it immediately. Text cells do not obey simple arithmetic operations like addition, subtraction, multiplication, etc. because the functions ignore them. As a regular user of excel, there is ample need for you to learn how to fix this anomaly.

 

How to convert text to numbers on excel

1. Using the ‘Convert to Number’ method

This method is used when you mistakenly type an apostrophe just before the number. You will notice a greenish triangle on the top left corner of the text.

 

 

 

 

Step1: Click and select all the target cells

Step 2: Look at the top right corner beside the cells; you will see a yellow diamond-shaped icon. Click it and scroll down to the option “convert to number.”

 

 

 

 

Within a blink, all the texts will be converted to numbers, and they will align to the right corner of the cells.

2. By changing the format of the cell

Step 1: Click and select all the target cells.

Step 2: Navigate and click the “home” button. Go ahead and click “Number.” Check the drop-down under the number format. You will see “General.” Select it.

Within a blink, all the selected-texts will be converted to numbers, and they will align to the right corner of the cells.

3. By Using the Value function

Extra Spaces, non-eligible characters, and trailing spaces can cause your number to appear as text. A combo of VALUE, TRIM, and CLEAN functions will do the magic for you.

Assuming you want to change cell B1 from text to number, we should use the formula below:

=VALUE(TRIM(CLEAN(B1)))

This formula will quickly convert your texts to numbers.

4. By Using the Paste Special Option

Step1: Type “1” in any of the cells. The cell must be empty before you type, and “1” must align to the right.

Step2: Select the cell and copy it.

Step3: Identify and select the target cell that contains texts.

Step4: Right Click, look for “Paste special,” select it.

Step5: A dialog box will show up under the paste special option. Proceed by selecting “Multiply.” The “Multiply” option is in the operation category. Complete the procedure by clicking, OK.

The texts will quickly convert to numbers.

All the methods highlighted above are quick and effective, so I’m hopeful that you now know how to convert your texts to numbers quickly.