In this third method you are performing a mathematical operation on the text by using Paste Special. The Third Way To Convert Text To Numbers In Excel: Paste Special (add zero) In these cases you can try a third option which I’m about to show you. I believe it has something to do with some rare and bizarre data formats. You try and try and it just won’t happen. The only drawback with this method is that sometimes it just doesn’t seem to want to work. Just leave “General” selected, click Finish and you’re done.
Then click Next to go to the third and final step.īelow is the step three window. To make this work, just make sure none of the boxes are checked, as below. This will bring up step two of the wizard, shown below. Choose “Delimited,” and then click on Next at the bottom. When you click on the Text to Columns button (after selecting your data), you’ll get the first of three screens as shown below. In the process, Excel magically converts the text to numbers. You’re basically going to “pretend” to split a column into multiple columns, but not allow Excel to actually do that. You will go to the Data tab as shown below, and basically “hack” the Text to Columns feature - after selecting your data. Here’s another pretty fast method that doesn’t make Excel slow to a crawl. The Second Way To Convert Text To Numbers In Excel: Text To Columns Therefore, I learned two other methods that do not suffer from this drawback, which I’ll share with you now. In fact, it may even cause Excel to crash. If you do this on a very large range with, say, 100,000 cells, it may take a very long time. Simply click on the option that says, “Convert to Number.” You can do this on one cell or on an entire range that you select. If you click on the little down arrow next to the yellow diamond, you’ll get a drop-down menu as shown below.
So how do I convert the text to numbers? The First And Easiest Way To Convert Text To Numbers In Excel: Convert To Number As shown below, when you hover over that yellow diamond, Excel will tell you it is formatted as text by saying, “the number in the cell is formatted as text or preceded by an apostrophe.” In this case there is no apostrophe so I know the data is formatted as text. When you’ve selected one of those cells, a little yellow diamond with an exclamation point inside appears to the left of the cell. If you didn’t notice above, when “numbers” are formatted as text, there’ll be little green triangles in the upper left of the cell. How Do I Know The Data Is Formatted As Text? You can see that the SUM function now works. What you will often want instead is for these “numbers” to be truly formatted as numbers - so that you can use them in formulas.īelow, I have converted the text in the left column to actual numbers in the right column. Therefore, it is useless in calculations (you can see that, when I summed the column, the result was zero). The data in the left column is formatted as text. Sometimes you get data and something doesn’t look right. 3 Ways To Convert Text To Numbers In Excel
0 kommentar(er)
