How to extract a substring in Excel

The stored data in excel comprises of various Alphanumeric string. The text consisting of certain characters of this alphanumeric string are considered as substring. Various types of information consist of a number of substrings. These substrings are mostly in form of numerals or alphabets. Substring is an essential part of any kind of data. Sometimes, we need to acquire a specific part of a substring from the whole substring.

Excel has made the extraction of substring super easy. Now excel users can easily access the feature of extracting substring. Excel has introduced several types of techniques in its program which are really helpful in extracting substring. In this article we have described some of these methods which are compatible for 2016\2019\mac\online versions.

1. Use the LEFT, RIGHT and MID functions

Excel has thousands of functions that are very handy in getting the job done. Below are three different functions that you can use to extract substring.

The LEFT function allows you to extract a specified number of characters from the left side of a text string. The syntax is as follows: LEFT(text, num_characters).

The RIGHT function enables you to extract a specified number of characters from the right side of a text string. The syntax is RIGHT(text, num_characters).

The MID function extracts a specific number of characters from the middle of a text string. Its syntax is: MID(text, start_position, num_characters).

Follow the below stepwise guide to use these functions to extract substrings in Excel.

• Suppose you have an Excel sheet with employee details like their name, number, and location.

• You want to extract the first name only of these employees, then use the following formula.
  “=LEFT(A2,4)” will select the A2 column and the first four letters starting from the left side.

• It will give you the following results showing the first name (first four characters) of every employee’s name.

• Similarly, using the Right function, we can extract the last name of employees.

• We will use the formula “=RIGHT(A2,4),” yielding the following results.

• It will give you the following results showing the first name (first four characters starting on the right) of every employee’s name.

• Using the Mid function, we will extract the employee number. Suppose mid-four numbers in our example show employee ID.

• To extract the employee ID, use the formula “=MID(B2,4,4)”. Column B starts with the 4th character and selects the following four characters.

• You can use this formula for any data; given the parameters right, you will have the desired text or numbers extracted.

Pros

• Easy to manipulate

• Compatible with all Excel versions

• Efficient and Time saving

Cons

• Not available for complex operation

• Lack of dynamic updating

2. Use the TRIM function

Another function that you can use to extract Substrings in Excel is the Trim function. This function is typically used for extra spaces from text, explicitly leading and trailing spaces. It is beneficial in cleaning up the data when copied from another application. It can only remove the ASCII space character (32) from the text. You can use the TRIM function by following the mentioned steps.

• Suppose we have data taken from external sources. This data contains names and has inconsistent spaces uneven.

• Using the TRIM function, we will remove all the additional unnecessary spaces.

• The function is used as, ‘=TRIM(A4)“, where A4 shows the column reference. It is an easy way to remove junk from the data and extract the actual data.

• Let us use the same function on alphanumeric data with uneven spaces.

• All the additional spaces between data have been removed, and you get consistent data.

Pros

• Convenient

• Easy to Use

• Widely Supported

Cons

• No In-Cell Modification

• Time-consuming

3. Use the MID and FIND functions

The MID and FIND functions are both useful text functions in Excel for manipulating and extracting substrings from text strings. The MID process removes a specific number of characters from a text string, starting at a specified position. In contrast, the FIND function determines the status of any particular character or text within another text string. The below tutorial shows how you can use these functions to extract Substrings in Excel.

• Suppose we have an Excel sheet with students' first and last names. We want to extract the previous names only of these students using the Mid and Find functions.

• We will use the following formula, “=MID(B2,FIND(” “,B2),100)”. In this formula, the MID function searches the string at B2 and starts the substring from the first character, here search function fetches the location of space and returns the integer value

• It will give us the output as the last name of each employee up to 100 characters.

Pros

• Versatile use

• Flexible Positioning

• Accurate extraction

Cons

• Complicated

• Complexity for dynamic extraction

4. Use Flash Fill

Flash Fill is an excellent feature in Excel that automatically fills in values in a column based on a pattern it recognizes in the adjacent column. It is straightforward to use, saves time, and aims to automate data entry and manipulation tasks, reducing the need for manual input. You can use the Flash Fill by following the below-mentioned tutorial.

• Suppose you have an Excel sheet containing scrap data and want to extract meaningful data like specific numbers, names, email addresses, and more.

• In our case, the data has alphanumeric values, and we want to extract only discounts. So, we will give the first column required numeric values as input.

• Then go to Data Tab, click Flash Fill, or press CTRL + E on your keyboard. It will automatically give you the numeric values from the data.

• Now we want to extract the alphabetic values as output. Simply input the alphabet, and it will yield the required results.

• You can also give your input in a specific format, and it will convert the whole column’s data. For example, I have the same data in a combined form, and I have to format it. I will only give the input in the first column and then press CTRL+E, and I will get the result in my whole column. 

• Similarly, if I want to convert these names into email addresses, I will simply enter the first name with the email address, and Flash Fill will do the rest.

Pros

• Automatically Extract data

• Automatically combine data

• Supports dynamic updates

• Visual Preview

Cons

• Precision issues in decimal data

• Pattern Recognition Limitations

FAQ about substring

Q: Can I extract multiple substrings at once in Excel?

Yes, you can extract multiple substrings at once in Excel using formulas or functions such as MID, LEFT, RIGHT, or text manipulation tools like Flash Fill.

Q: How to extract substrings after specific characteristics in Javascript?

You can use JavaScript's substring or slice methods to extract substrings after specific characters or patterns.

Q: How to extract numbers from a string in Excel?

You can use the combination of the MID, FIND, and ISNUMBER functions in Excel to extract numbers from a string.

Summary

Extracting substrings in Excel is necessary for cleaning messy and junk data. In addition, it makes data analysis and manipulation easy, and it saves time. You can use Excel’s built-in functions like Left, Mid, Right, Flash Fill, and Find to extract the desired substrings. Usually, these functions will help you extract substrings; unless your data is massive, you can rely on these functions.

If you find these functions complex or uncomfortable with using formulas, you can try other tools like WPS Office, which has a powerful Spreadsheet tool that will make your life easy in extracting substrings. Try WPS Office once, and you will never regret your decision.