Extracting data from Text
Previously we wrote about how to combine text from different cells. It’s time to look at the opposite of how to extract data from text from cells into different cells. We will use different functions and each function will be defined in a short text.
First Example: Extracting First or Second Name from Full Name
First Name can be extracted by using Flash Fill method, Combination of LEFT and Search Function and Combination of LEFT and Find Functions. Let’s look at each of the methods below:
Flash Fill Method
This is by far the easiest method of extracting the first name or the second name. In Cell D4 Enter Mike manually. Now click in cell D5 and go to the data tab and select Flash Fill. Your list will automatically be filled. This method though simple but at the same time has limitations too. Flash method list not updated automatically in case there is a new entry or you need to an edit a name. You have to apply the Flash fill button in case of new addition and in case of change, you need to delete the old first name and then apply the flash fill again.
In this method, we will use the search function to find the position of the text. In our case, we are searching for the space between first name and second name. We added -1 since we do not want the space to be counted. The second step is to apply the LEFT function. LEFT function extract text from the given text and the number of characters are provided by the search function. Hit the enter button and copy the formula down the rows.
LEFT+Find Functions perform exactly the same way as the LEFT+Search Function. Though there is the difference between the search function and Find Function but in this case, both work the same way.
Second Example: Extracting Second Name From the Text
Since Flash Fill method works the same way and we have covered it earlier in detail. Simply enter the second name and then come to the second row and apply the flash fill.
In this example, we will use the LEN function first which will give us the total length of the characters in the name. The next step is to deduct the characters from left side including the space. The characters remain will be the ones come after space. At last, we will use the RIGHT function which will extract the data to the right of space.
The functions work the same way as the search function, in this case, so we will not go into much detail here.