How to extract text from Excel cells?Excel is widely used for office and personal use. It consists of enormous functions and formulas for the given data, which helps retrieve it quickly and easily. The data entered by the user is either a numeric value, alphabet or mathematical symbol. Sometimes the data entered by the user is a combination of numeric values and alphabets. Extracting the respective or required text from the data present in the cell manually is a difficult one. To rectify this problem, Excel provides some powerful text functions that help extract the required text from the given data. What is called a Substring in Excel?Before explaining the text functions, the concept of "Substring" is explained here for better understanding. A substring is extracting or parsing the string from the combination or group of strings. It is a subset or part of the given string. In general, the example of a substring is as follows, The result will be displayed as AMYAK, the 5 characters from the second position. Excel Text functionsThere is no substring function present in Excel. The various text and default functions are present in Excel to parse the text from the cell. The various text functions are as follows, Among the various text functions, the main text functions which are used for extracting the text are MID, RIGHT, LEFT, LEN, FIND, REPT, SUBSTITUTE, TRIM and MAX. Some of the text functions are mentioned above in the worksheet. Let's have a look at an example for each Text function. Example 1: How to extract the middle of the text from the given data?To extract the middle of the text from the given data, the steps to be followed are,
From the above worksheet, the MID function parses the text orange. Here 'o' is mentioned as the 7th position as the comma is included in the 6th position. Example 2: How to extract the left of the text from the given data?To extract left of the text from the given data, the steps to be followed are,
The above worksheet will display the result as plums, five-count characters parsed from the left. For the same example, the formula can be modified as =LEFT (A1, FIND ("-", A1)-1). The final result will be as same as Plums. Here in this formula, the FIND position finds the position of the "-" and subtracts one from the position of the dash value. The final result will be the position of the leftmost character, which is parsed from the data. From the above worksheet, the result will be displayed as "Plums" using the FIND function. Example 3: How to extract the right of the text from the given data?To extract right of the text from the given data, the steps to be followed are, Step 1: Enter the data in the worksheet, namely A1. Step 2: Select a new cell and type the formula as =RIGHT (A1, 6). Here A1 indicates the cell name, and 6 indicates the count of characters present on the right side. Step 3: The result will be displayed in the selected cell, parsed right from the given data. The above worksheet will display the result as Mexico. A six-count character is parsed from the right of the given data. Example 3.1: How to extract the right of the text present after a dash of any length? LEN and FIND extract any length of the character present after the dash. The steps to be followed are, Step 1: Enter the data in the worksheet, namely A1. Step 2: Select a new cell and type the formula as =RIGHT (A1, LEN (A1)-FIND ("-", A1)). Here LEN function returns the length of the string, and the FIND function finds the position of the dash. Hence from the total length, the position of the dash value is subtracted, and the rightmost character is parsed from the data. Step 3: The result will be displayed in the selected cell, parsed right from the given data. Here the formula returns the total length of the string value as 7 and positions the dash as 4. Hence 7-4= 3. This is parsed from the right of the data. Example 4: How to extract the username from the mail-id?Here in this example, the given data set contains various email-ids. The user wants to retrieve the username in a separate cell. The steps to be followed are, Step 1: Enter the data in the required column A1:A5 Step 2: Identifying the pattern type helps modify the formula more easily and quicker. Here the pattern is '@', which lies between the username and domain name. Select a new cell where the user wants to display the result and type the formula as =LEFT (A1, FIND ("@", A1)-1). The formula uses'@'acts as a reference to get the user name in the separate cell. Step 3: The result will be displayed in the selected cell, which contains the username of the respective email id. From the above worksheet in cell A1, the FIND function in the formula positions the '@' symbol in the 4th position and subtracts 1 from it. Hence the result will be 3, which is the count of characters parsed from the left of the given character. Similarly, this method is followed for all the data in the worksheet. Example 4.1: How to extract the domain name from the mail-id? In example 3, the username is extracted from the data. The same concept is followed here with a minor difference, and the character is parsed from the data's right side. Here in this example, the given data set contains various email-ids. The user wants to retrieve the domain name in a separate cell. The steps to be followed are,
From the above worksheet in cell A1, the FIND function in the formula positions the '@' symbol in the 4th position. The user wants to extract the character present after the '@' symbol. Hence the total length of the string is founded and subtracted with the character present before the '@' symbol. Finally, the character present towards the right of the '@'symbol is displayed as a result. Similarly, this method is followed for all the data in the worksheet. Example 5: How to extract the domain name from the mail-id without.com?To extract the domain name without .com, the formula is modified as follows, The MID function extracts the specified characters from the starting position from the above formula. FIND ("@", A1) +1 indicates the starting position, right of @. FIND (".", A1)-FIND ("@", A1)-1) counts the character between '@' and '.' The steps to be followed are, Step 1: Enter the required data in column A1:A5 Step 2: Select a new cell where the user wants to display the result and enter the formula as =MID(A1, FIND("@", A1)+1, FIND(".", A1)-FIND("@", A1)-1) Step 3: The result will be displayed in the selected cell, which is the domain name without .com. From the above worksheet, the result displayed is Gmail and yahoo without.com. Similarly, this method is followed for other data if the user wishes to display the domain name, as shown in the above method. Example 6: How to extract the substring using a delimiter in Excel?Using text functions, the substring is extracted from the given text. If the original text is altered, the result also gets altered where the formula modifies the result based on the data. Here is an alternate method called 'Text to Column' which involves the same process of extracting the substring quickly using markers. The step to be followed to extract the substring using 'Text to column' is as follows,
If the user wants to extract the gmail.com to Gmail and .com the same method is repeated to extract the substring from the data. Example 7: How to use 'Find and Replace' to extract the substring from the data?Here in this example set of email- IDs are given. The job is to parse the username from the given mail-id. The steps to be followed are,
From the above worksheet, only the username is displayed. Here @ an asterisk * is combined to extract the string from the text. An asterisk is a wildcard character which is used to remove the characters. The @ symbol represents the characters counted which are present after it. A @* represents a character that is removed which is present after the @ symbol. Hence the final result will be the username of the given data. Example 8: How to extract two characters between the parentheses in the dataSometimes the data are present between slash, brackets, parentheses or braces. To extract the data which is present between the braces, the MID and FIND function is used. The steps to be followed are,
The above worksheet shows the result in column B1:B3, where the MID function extracts two characters from the data. Here for cell A1, the formula will be like =MID (A1, 6+1, 2).Similarly, the same method is followed for all the data in the worksheet. Example 8.1 How to extract complete characters between the parentheses in the data? From example 8, only two data are extracted from the data. To extract complete data which is present in the parentheses, the steps to be followed are, Step 1: Enter the data in the respective cell, namely A1:A3. Step 2: Select a new cell and enter the formula as =MID (A1, FIND ("(", A1) +1, FIND (")", A1)-FIND ("(", A1)-1). In the formula, the position of the opening parentheses and value one is subtracted from the position of the closing parentheses. The final result will be the position of the substring. Step 3: Press Enter. The result will be displayed in the selected cell containing two characters. From the above worksheet, the MID and FIND function returns the complete value, which is present between the parentheses. Example 9: How to extract a selective or specific substring from the given data?To extract the specific substring from the given data, the steps to be followed are,
The step-by-step process of extracting the specified or selected substring is explained in step1 to step. To do this method in a single step, the formula is modified as follows, Flash Fill Method"A flash-fill method in Excel was used to extract the substring without a formula. It is an alternative and quicker option which parses the string from the given data." Example 10: How to extract the string from the text using Flash Fill Method?The steps to be followed to use the Flash Fill method are as follows,
Example 11: How to use the flash fill method to combine the last name and first name present in the separate column?In example 10, the flash fill method displays the number pattern. Similarly, to combine the first name and last name, the steps to be followed are,
From the above worksheet, the data present in C1:C5 follows the same pattern. SummaryThe above tutorial explains the various methods and functions to extract a substring and text from the data. |