Microsoft Excel is great at working with both numbers and text—but if you’re using both in the same cell, you might run into some difficulty. Fortunately, you can extract numbers or text from cells to work with your data more efficiently. We demonstrate several options, depending on the format that your data is currently in.
Excel Numbers Formatted as Text
This is a common situation, and—fortunately—very easy to deal with. Sometimes, cells that contain only numbers are incorrectly labeled or formatted as text, preventing Microsoft Excel from using them in operations.
You can see in the image below that the cells in column A are formatted as text, as indicated by the number format box. You might also see a green flag in the top left corner of each cell.
Convert Text to Number in Excel
If you see the green flag in the top left corner, select one or more cells, click the warning sign, and select Convert to Number.
Otherwise, select the cells and, in the Number Format menu in the Ribbon, select the default Number option.
If you need more granular options, right-click the highlighted cell/s and select Format Cells, which will open the respective menu. Here, you can customize the number format and add or remove decimals, add a 1,000 separator, or manage negative numbers.
Obviously, you can also use the Ribbon or Format Cells options outlined above to convert a number to text, or text to currency, time, or any other format you desire.
Apply Number Formatting With Excel’s Paste Special
For this method to work, you’ll need to enter a number (any number) into a cell; it’s important that this cell is also formatted as a number. Copy that cell. Now, select all the cells that you want to convert to the number format, go to Home > Paste > Paste Special, select Formats to paste only the formatting of the cell you copied initially, then click OK.
This operation applies the format of the cell you copied to all selected cells, even text cells.
Extract Numbers or Text From Mixed Format Cells
Now we get to the hard part: getting numbers out of cells that contain multiple formats of input. If you have a number and a unit (like “7 shovels,” as we have below), you’ll run into this problem. To solve it, we’re going to look at a couple different ways to split cells into numbers and text, letting you work with each individually.
Separate Numbers From Text
If you have a lot of cells that contain a mix of numbers and text or multiples of both, separating them manually might take a monumental amount of time. To get through the process faster, you can use Microsoft Excel’s Text to Columns function.
Select the cells that you want to convert, go to Data > Text to Columns, and use the wizard to make sure the cells come out correctly. For the most part, you’ll just need to click Next and Finish, but do make sure you pick a matching delimiter; in this example, a comma.
If you only have one- and two-digit numbers, the Fixed Width option can be useful too, as it will only split off the first two or three characters of the cell. You can even create a number of splits that way.
Note: Cells formatted as text will not automatically emerge with a number formatting (or vice versa), meaning you might still have to convert these cells as described above.
Extract a Number or Text From a Delimited String
This method is a bit cumbersome, but works very well on small datasets. What we assume here is that a space separates the number and text, though the method also works for any other delimiter.
The main function we’ll be using here is LEFT, which returns the leftmost characters from a cell. As you can see in our dataset above, we have cells with one-, two-, and three-character numbers, so we’ll need to return the leftmost one, two, or three characters from the cells. By combining LEFT with the SEARCH function, we can return everything to the left of the space. Here’s the function:
=LEFT(A1, SEARCH(" ", A1, 1))
This will return everything to the left of the space. Using the fill handle to apply the formula to the rest of the cells, this is what we get (you can see the formula in the function bar at the top of the image):
As you can see, we now have all the numbers isolated, so we can manipulate them. Want to isolate the text as well? We can use the RIGHT function in the same way:
=RIGHT(A1, LEN(A1)-SEARCH(" ", A1, 1))
This returns X characters from the right side of the cell, where x is the total length of the cell minus the number of characters to the left of the space.
Now you can also manipulate the text. Want to combine them again? Just use the CONCATENATE function with all the cells as inputs:
=CONCATENATE(E1, F1)
Obviously, this method works best if you just have numbers and units, and nothing else. If you have other cell formats, you might have to get creative with formulas to get everything to work right. If you have a giant dataset, it’ll be worth the time it takes to get the formula figured out!
Extract a Number From One End of a Continuous String
Now what if there’s no delimiter separating your number and text?
If you’re extracting the number from the left or right of the string, you can use a variation of the LEFT or RIGHT formula discussed above:
=LEFT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))
=RIGHT(A1,SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{"0","1","2","3","4","5","6","7","8","9"},""))))
This will return all numbers from the left or right of the string.
If you’re extracting the number from the right of the string, you can also use a two-step process. First, determine the location of your first digit in the string using the MIN function. Then, you can feed that information into a variation of the RIGHT formula, to split your numbers from your texts.
=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
=RIGHT(A1, LEN(A1)-B1+1)
Note: When you use these formulas, remember that you might have to adjust the column characters and cell numbers.
Extract Numbers From Both Ends of a Continuous String
With the strategies above, you should be able to extract numbers or text out of most mixed-format cells that are giving you trouble. Even if they don’t, you can probably combine them with some powerful text functions included in Microsoft Excel to get the characters you’re looking for. However, there are some much more complicated situations that call for more complicated solutions.
For example, I found a forum post where someone wanted to extract the numbers from a string like “45t*&65/”, so that he would end up with “4565.” Another poster gave the following formula as one way to do it:
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
To be completely honest, I have no idea how it works. But according to the forum post, it will take the numbers out of a complicated string of numbers and other characters. The point is that, with enough time, patience, and effort, you can extract numbers and text from just about anything!
You just have to find the right resources.
Read the full article: How to Extract a Number or Text From Excel
Read Full Article
No comments:
Post a Comment