Excel is undoubtedly a powerful application of Microsoft Office suite.
This is very wide and there are plenty of Excel functions that help the user to use this complex application quite easily.
But in some cases, Excel becomes a big disaster.
Today in this article I am going to describe a common problem how to convert text to number in Excel.
When the user import data from some source, like Access or text files then in some cases the numbers might get formatted as text in cells
Well, this is a quite frustrating situation as, when the numbers are formatted or stored as text the entire calculations and sorting goes wrong.
But don’t take tension as there are several methods that will help you to convert text to number in Microsoft Excel.
This issue causes the functions such as SUM and AVERAGE to ignore values in these cells. These text strings might hold an actual text in addition to the numbers you need to covert
However, if you want to become more productive in Excel and want to learn powerful Excel function and features, then Learn Advance Excel Easily
Steps to Convert Text to Numbers in Worksheet:
For converting the text to numbers in a worksheet, try any of the below-given methods and check which help you out.
Please Note: Every given method assume you have already changed any cell number formatting in cells to General. Follow the steps to do so:
- On Format menu in Excel > click
- Then on Number tab > under Category > click General and > OK.
Now follow the given methods one by one:
Step 1: Retype Values in the Cells
On the Format menu,> click Cells and then > click Number tab for changing the number format of the cells. After that retype the numbers
Hope doing this will help you to convert text to number in Excel.
You May Also Read:
- 6 Ways to Use Pivot Table to Analyze Quarterly, Monthly & Yearly Trends
- 8 Easy Excel Filters To Save Time, Money and Get Accurate Data
- 16 Advanced Excel Skills To Succeed at Office
Step 2: Select “Edit Directly in Cell”
To do this, follow the given steps and check if this helps you to convert text to number in Excel.
- First in the Tools menu > click Options (in Windows) > or In Mac click Preferences on the Edit menu.
- Then on the Edit tab,> check that the Edit directly in cell check box is selected > and click OK.
- And double click the cell you want to format > press Enter.
Step 3: Use Paste Special with Multiply
Doing this will help you to convert text to numbers in Excel, so here follow the steps to do so:
- Enter the value 1, in any blank cell.
- After that select, the particular cell where you type 1 > on the Edit menu click Copy.
- And with the values select the cells that you need to convert to numbers.
- Next on Edit menu > click Paste Special.
- Then under Operation > click Multiply, and Under Paste > click Values > click OK.
Step 4: Eliminate Hidden Characters and Spaces
This step works well if the data is arranged in a single column or row. This tip utilizes the TRIM function and the CLEAN function for removing extra spaces and nonprinting characters that might be imported with the file.
Following example assumes the data is in column A and starts in row 1 ($A$1).
Follow the given steps to use this example:
- To the right of column, A insert a column by selecting column B > click Columns on Insert menu
- The in the first cell of the inserted column (B1), type the following
- After that, in column B > to the right of the cell choose all cells that contain data in column A
- Next, on the Edit menu,> point to Fill > then click Down.
The new column contains values of text in column A
- Then with same range selected > on the Edit menu > click Copy
- Select cell A1 > click Paste Special on Edit menu. Then under Paste > click Values > click OK to paste the converted values back on top of column A.
- And by selecting the column to delete the column B > click Delete on Edit menu
You can see the text that was in column A is in number format now.
Step 5: Utilize Visual Basic for Applications Procedure
Microsoft offers programming examples for illustration only, and without warranty either expressed or implied.
It includes but is not limited to, the implied warranties of merchantability or fitness for a particular reason.
In this article, we suppose that you are familiar with a programming language that is being confirmed and with the tools that are utilized for creating and to debug procedures.
Microsoft support engineers can help in describing the functionality of particular process but they would not change these examples to provide added functionality or build procedures to meet your particular requirements.
Create a Visual Basic for Applications macro to re-enter the numbers in the chosen cell or range of cells.
Follow the steps to do so:
- Choose cells that exhibit the behaviour.
- If you haven’t done so, then on the Format menu click Cells > click the Number tab to modify number format of the cells
- Next, on Tools menu,> point to Macro > click Visual Basic Editor
- And on the Insert menu > click Module.
- After that type following macro code in the new module:
- Next, on File menu,> click Close and Return to Microsoft Excel
- Reselect affected cells if not already selected.
- Next on Tools menu > point to Macro > click Macros. In the Macro name list > click Enter_Values > click Run.
Step 6: Utilize Text to Columns
This method works best of data is arranged in a single column. The example presumes that the data is in column A and starts in row 1 ($A$1). Follow the steps to do so:
- Choose one column of cells that hold the text.
- And on the Data menu > click Text to Columns.
- After that under Original data type > click Delimited > click Next.
- Next under Delimiters > click to choose the Tab check box > click Next.
- And under Column data format > click General.
- Then click Advanced > make any appropriate settings for the Decimal separator > Thousands separator and click OK.
- Click Finish.
And that all your text is converted to numbers.
Well, this is all about how to convert text to numbers in Excel.
I tried my best to put together the possible steps that will help you out.
Follow the given steps one by one and easily convert text to numbers.