How to extract data from an Excel cell

Currently Microsoft Excel is one of the most used programs worldwide to make spreadsheets , in this sense you can use this tool to have control over various processes that require simple and complex calculations . So it will allow you to save time when making use of the functions or formulas to obtain the desired result.

This time we will be talking about how to extract numbers and data from an Excel cell in a simple way, because you will see that there are different ways to carry out this work, but many of them involve a great level of work, by using nested formulas and even development programming in VB to get the result.

Index(  )

1. Steps to extract numbers from alphanumeric text string in Excel
   1. Find the starting position of the number
   2. Find the length of the number
   3. Using quick fill
2. How to extract data from an Excel cell
   1. Characters at the beginning or end
   2. Intermediate characters
   3. Extract the first word
3. Advantages of using quick fill to extract data in Excel
4. Final thoughts

Steps to extract numbers from alphanumeric text string in Excel

In many cases, companies that have catalogs need to separate the numbers from the letters and that is why they mostly go to the tool that Excel provides us. Now, for this, we must follow some procedures.

In this sense, extracting the numbers cannot be done by a single formula. Although it is not a complicated process, it does require a series of functions that are implemented directly. That is, each function has another function as an argument and so on with each one 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 one is the FIND function . Now this function works with two arguments; the first one requires the characters or numbers that we are going to search for. The second argument requires the position of the cell where the string is located in which we want to find the initial position of the number.

In our case, the find function takes in its first argument or an array that contains all the numbers from 0 to 1 , separated by commas and grouped by square brackets, this indicates that it is a matrix. Now, assuming that 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 found. In the case of all those values ​​that were not found within the string, the result will be an error value.

This is where the need to use our second function, called IFERROR, arises. Well, this function includes a condition that verifies 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 is the data where the error value is verified , which can come in the form of cells or arrays. The second is the value for which this data will be replaced. An example of our case would be as follows: IF.ERROR ({# VALUE!, 9,3, # VALUE!, # VALUE!, # VALUE!, # VALUE!, 5,10, two},’ ‘).

Now the third function is MIN . This is the simplest of all; as it requires a single argument of numeric data. The function extracts the minimum value of those 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 function is used to obtain each of the characters of a string individually in a resulting array.

To implement the EXTRAE function, three arguments are needed; we will explain the first two. Well, the first argument is about the position of the cell where the string is located . As for the second argument, it is the ROW function.

The ROW function requires a single argument, which expresses the number of characters that are extracted from the string . This argument can be stated in the form $ 1: $ 50. Which indicates that it is necessary to extract from the first character to the number 50. This assuming that we do not have any string that exceeds this amount.

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

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

Lastly, we will implement the COUNT function . It requires a character argument and returns the number of numbers they have.

Using quick fill

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

1. Considering that you have a column with a list of alphanumeric datawith certain patterns, you can place in the columns you want, but preferably close to this, the column where you will extract the numerical data and the column of the data (Letters)
2. Assuming that in the list or column the first alphanumeric data you have is “ABCP123NUM”, you will need to manually write the expression 123 in the column of numeric data
3. Now write in the data column characters (Letters) the remaining expression, for example “ABCP NUM” or “ABCPNUM”. Note that both cases can be valid, however, you will give the tool the way you need it to extract the data. In the first case you have separated into two groups by a space and in the second case you have the expression without spaces.

How to extract data from an Excel cell

1. Once you have carried out the previous steps, you must choose the expression that you wrote in the numeric data columntogether with the empty spaces corresponding to the expressions that you want Excel to extract, that is, assuming you have 300 alphanumeric data.
2. After manually writing the first one, you will have to choose the first data written by you and in the same column the other 299 missing spaces.
3. With the range chosen, go to the Data tools tab and click on quick fill. The result will be the extraction of the numerical data in the empty cells, according to the pattern that you gave to the first extracted number.
4. Now to extract the Lyrics data you must do the same process as above, but considering that the working column will be the one corresponding to the data (Lyrics). In the same way, choose the range of cellsincluding the first one where you manually wrote the letters extracted from the first expression and click on quick fill.

Characters at the beginning or end

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

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

On the other hand, if you want to obtain the last characters of a string , the necessary 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 that is located 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 where you start to extract . The last one is about the number of characters to extract.

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

Extract the first word

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 found for the first time. In this case, the first argument is a space enclosed by single quotes like this ”; this is because space is our indicator for the end of a word . The second argument is the position delt exto.

The second function is LEFT, which we explained earlier. For this case, the second argument is to the FIND function minus one position; since 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’.

Advantages of using quick fill to extract data in Excel

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

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

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

Final thoughts

Finally, it is important that you have or count on various tools to perform your tasks efficiently, that is why it will be good for you to know how to use the Extract function in Excel , since it will be a complement to extract necessary data from a list.

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