How to extract numbers and data from an Excel cell in an easy way

Who I am
Philippe Gloaguen

Author and references

Microsoft Excel is currently one of the most used programs in the world to create spreadsheets, in this sense this tool can be used to have control over various processes that require simple and complex calculations . So it will allow you to save time when using functions or formulas to get the desired result.

This time we will talk about how extract numbers and data from an Excel cell into easy way, because you will see that there are several ways to do this job, but many of them involve a great level of work, using nested formulas and even development programming in VB to get the result.

    In many cases, companies that have catalogs must separate numbers from letters and that is why they mainly turn to the tool provided by Excel. Now, for this, we have to follow some procedures.

    In this sense, the extraction of numbers cannot be done by a single formula. While it's not a complicated process, it requires a number of functions which are implemented directly. That is, each function has another function as an argument and so on with each of them.

    Find the starting position of the number

    To find the starting position of the number in Excel, you need to implement three functions. The first is the FIND function . Now this function works with two arguments; the first requires the characters or numbers that we are going to look for. The second argument requires the position of the cell where the string is in which we want to find the starting position of the number.

    In our case the find function has in its first argument or an array that contains all numbers from 0 to 1 , separated by commas and grouped by square brackets, this indicates that it is an array. Now, assuming the string is in cell D2, the function would be as follows: FIND ({0,1,2,3,4,5,6,7,8,9}, D2).

    This expression will give us a resulting matrix , in which we will see in which position of the chain each of the indicated values ​​is located. In the case of all those values ​​that were not found within the string, the result will be an error value.

    Read How to connect Nintendo Switch to a TV without using the dock step by step

    This is where the need arises to use our second function, called SEERROR. Well, this function includes a condition that check if there is an error value in the indicated data ; and if it exists, it replaces them with another value that we indicate.

    It works with two arguments, the first being the data in which the error value is checked , which can come in the form of cells or arrays. The second is the value for which this data will be substituted. An example of our case could be the following: IF.ERROR ({# VALUE !, 9,3, # VALUE !, # VALUE !, # VALUE !, # VALUE !, 5,10, 2}, '').

    Time the third function is MIN . This is the simplest of all; as it requires a single numeric data argument. The function extracts the minimum value of these arguments. Continuing with our, the function would be the following: MIN ({, 9,3 ,,,,, 5,10,2}).

    Find the length of the number

    Knowing the length of the number within a string requires three functions. To start with, we need the EXTRAE function. Well, this feature comes used to get each of the characters in a string individually in a resulting array.

    Three arguments are required to implement the EXTRAE function; we will explain the first two. Well, the first argument it concerns the position of the cell where the string is located . As for the second argument, it is the ROW function.

    The ROW function takes a single argument, which expresses the number of characters extracted from the string . This argument can be expressed in the form $ 1: $ 50. Which indicates that we need to extract from the first character at the number 50. This is assuming we have no string that exceeds that amount.

    Now, an example of the extract function would be as follows: EXTRACT (D2, ROW ($ 1: $ 50), 1).

    Read How to increase and improve the sound of my headphones with the best apps

    Since the EXTRAE function gives us an array of each character separately, we must discard all non-numeric characters . To do this, we need to multiply the EXTRAE function by 1. This leaves only the numeric characters left and the rest is converted to error values.

    Finally, we will implement the COUNT function . Takes a character argument and returns the number of numbers they have.

    Using quick fill

    use the quick fill function you have to follow the following steps, but not before considering that the alphanumeric data list you have must have a schema, otherwise unfortunately this option will not work and you will have to make use of resources like how to program in VB or generate nested functions to get the result :

      Characters at the beginning or at the end

      I characters at the beginning of a chain obtained with the LEFT function . This takes two arguments, the first is the cell position and the second is the number of characters that will be extracted, counting from the first character.

      Example, if the string found in cell D2 is 'ABCDEF123GHI' and the function is LEFT (D2,5). The result obtained is the string 'ABCDE'.

      If instead you want to get the last characters of a string , the required function is RIGHT. Like the previous one, it requires two arguments; the first is the position of the cell and the second is the number of characters to extract.

      For example, if the string found in cell D2 is "ABCDEF123GHI" and the function is RIGHT (D2,6). The result will be the string '123GHI'.

      Intermediate characters

      Intermediate characters are extracted using the EXTRAE function. Now, you need to provide three arguments. The first is the position of the cell where the text is located. The second is the position from which you start to extract . The last one concerns the number of characters to extract.

      Example, in cell D4 there is the string 'GHIJKLMN' and the function is EXTRAE (D4,3,4). The resulting string will be 'IJKL'.

      Read How to Prevent Carpal Tunnel Syndrome Using These Basic Peripherals on Your PC

      The first word is obtained by using two functions. The first is the FIND function, this is used to get the position of a part of the string when it is first found. In this case, the first argument is a space enclosed in single quotes like this ''; this is because the space is our indicator for the end of a word . The second argument is the position of the text.

      The second function is LEFT, which we explained earlier. In this case, the second argument is the FIND function minus a location; so long as this is how the position of the last letter of the first word is calculated.

      Example, if the following text 'abcde fghi jklmn' is found in cell D2 and we use the LEFT function (D2, FIND ('', D2) -1). The result is the string 'abcd'.

      There are many benefits you will have using this Excel tool, but undoubtedly the most important will be the saving of time , because you will see that for tables with little data, using this function will not make much difference.

      However, when you have a few hundred or thousands of data to analyze, it will be very helpful to have this process, because maybe you will save hours of work that you can use to carry out other activities.

      On the other hand, you will have another important direct advantage and that is that you will be able to avoid data transcription errors which are very common when you have to manually manage huge amounts of data.

      Final thoughts

      Finally, it is important to have or rely on various tools to perform your tasks efficiently, that is why it will be useful for you to know how to use the Extract function in Excel, as it will be a complement to extract i give needed from a list.

      Also, you should know that some versions of Excel do not have the quick fill option enabled , for this you can consult the procedure to enable fast filling on the official Microsoft website.

      add a comment of How to extract numbers and data from an Excel cell in an easy way
      Comment sent successfully! We will review it in the next few hours.