Microsoft Excel is great for working with numbers as well as text Saving Time With Text Operations In Excel Saving Time With Text Operations In Excel Excel can do magic with numbers, and it can handle characters just as well. This manual shows how to parse, convert, replace, and edit text in spreadsheets. These fundamentals will allow you to perform complex transformations. Read More Fortunately, you can extract numbers or text from cells to work with your data more efficiently. We demonstrate several options, depending on the format your data is currently in.
This is a common situation and fortunately very easy to handle. Sometimes cells that contain only numbers are incorrectly labeled as text, preventing Microsoft Excel from using them in operations. You can see in the image below that the cells in column A are stored as text, as indicated by the green flag in each cell, as well as the format box, both of which are highlighted below.
To solve the problem, simply click on a cell, click on the warning box and select “Convert to Number.” That's all about it! This can be done on multiple cells by selecting all of them and clicking the warning box, but it probably won't be as efficient as the next method.
If you have a lot of cells that need to be repaired, using this method can take a monumental amount of time. To get through the process faster, you can use Microsoft Excel Text to Columns. How to convert delimited text files into Excel spreadsheets. How to convert delimited text files into Excel spreadsheets. Use Microsoft Excel to import data from a text file to a spreadsheet. The Text Import Wizard helps you import delimited text data and organize it neatly. Read more function. Select the numbers you want to convert, go to Data> Text to Columns , and use the wizard to make sure the numbers come up correctly (for the most part, you'll just have to click Next and Finish without messing with any of the settings).
The text will be converted to numbers and you're done. Note that this only works on one column at a time, so if you have many columns you may want to use this final method.
For this method to work, you will need to enter the number 1 in a cell (it is important that it is in numeric format). Select that cell and copy it. Now, select all the numbers you want to convert to number format and press Edit> Paste Special . Select “Multiply” from the Operation section and press OK.
All the numbers you selected will be converted to the General format, which recognizes numbers. The text cells will also be converted to the General format, which shouldn't cause any problems.
Now we get to the hard part:getting numbers from cells that contain multiple input formats. If you have a number and a unit (such as "7 spades," as we have below), you will run into this problem. To figure it out, we'll look at a couple of different ways to split cells into numbers and text, allowing you to work with each one individually. The first method is a bit cumbersome, but it works very well on small datasets.
The main function we'll use here is LEFT, which returns the leftmost characters in 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 characters of one, two, or three characters. By combining LEFT with the SEARCH function 4 Excel Find Functions to Find Spreadsheets Efficiently 4 Excel Find Functions to Find Spreadsheets Efficiently In a giant Excel spreadsheet, CTRL + F will only get you Until now. Be smart and let the formulas do the hard work. Search formulas save time and are easy to apply. Read More Here is the function:
= IZQUIERDA (A1, BUSCAR ("", A1, 1)) This will return everything to the left of the space. Using the Fill Handler How to Save Time in Excel Using the Fill Handler How to Save Time in Excel Using the Fill Handler Mastering the fill handler is the first step to becoming an Excel wizard. Read More
As you can see, we now have all the numbers isolated, so they can be manipulated. Do you want to isolate the text too? We can use the RIGHT function in the same way:
= DERECHA (A1, LEN (A1) - BÚSQUEDA ("", 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 the text can also be manipulated. Do you want to combine them again? Just use the CONCATENATE function with all cells as inputs:
= CONCATENADO (E1, F1)
Obviously, this method works best if you only have numbers and units, and nothing else. If you have other cell formats, you may have to get creative with formulas to get everything to work properly. If you have a giant data set, it will be worth the time to get the formula!
The Text to Columns feature is useful for number-only columns, but it can also make life easier if you have mixed-format cells. Select the column you want to work with and click Data> Text to columns . You can then use the wizard to select a delimiter (a space is usually best) and have your column split the way you want.
If you only have one- and two-digit numbers, the Fixed Width option can also be useful, as it will only split the first two or three characters of the cell (you can create a series of splits if you like, but I'll save a full explanation of the fixed-width split for another item).
If your dataset contains many columns and you prefer not to use Text to Columns for each of them, you can easily achieve the same effect using a quick export and import. First, export your spreadsheet as a comma-separated values (CSV) file. Click File> Save As... and save your file as CSV.
Now, open a new spreadsheet and click File>Import... Select your CSV file and use the import wizard to split your data into two columns (you'll use the exact same steps you did with the Text to Columns wizard). By selecting “Space” in the Delimiters section, you will tell Microsoft Excel to split the data where it finds a space, which will isolate the numbers and text.
Click Finish when you're done, and you'll be left with a new spreadsheet that will split your columns in half. Of course, if you have more than one space in a cell, you'll end up with more than two columns, as you can see here:
Unfortunately, there is no good solution to this using this method; You'll just have to concatenate the cells again.
With the above strategies, you should be able to extract numbers or text from most of the mixed-format cells that are giving you trouble. Even if they don't, you can probably combine them with some of the powerful text features included in Microsoft Excel to get the characters you're looking for. However, there are much more complicated situations that require more complicated solutions.
For example, I found a forum post where someone wanted to extract the numbers from a string like “45t * and 65 /”, so that it ends with “4565.” Another poster gave the following formula as a way to do it:
= SUMPRODUCTO (MEDIO (0 y A1, GRANDE (ÍNDICE (NÚMERO (- MEDIO (A1, FILA ($ 1: $ 25), 1)) * FILA ($ 1: $ 25), 0), FILA ($ 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 remove the numbers from a complicated string of numbers and other characters. The point is, with enough time, patience, and effort, you can extract numbers and text from just about anything! You just need to find the right resources Need help with Excel formulas? 7 resources to check out Need help with Excel formulas? 7 Resources to Reference Excel is the gold standard of spreadsheets. Si debe usar Excel y, sin embargo, familiarizarse con él, estos recursos lo introducirán rápidamente a lo básico y más. Read more.
Ahora que tiene una mejor idea de cómo obtener números y letras de celdas de formato mixto, queremos saber qué otras estrategias ha usado para hacer lo mismo. Publique sus locas fórmulas de Excel, procesos o cualquier otra cosa que pueda ser útil en los comentarios para que todos podamos aprender de ellos.!