How to extract a substring in Excel
A free Office suite fully compatible with Microsoft Office
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.
How To Extract Start Of A Substring By Using LEFT Function?
A substring consists of different parts which can be easily separated one by one. To separate the start of the substring we should use LEFT function. These function is very useful for extracting the first part of a substring. The steps of this function are as follows. This method works with 2016\2019\mac\online versions.
1. First we will select the cell containing the desired substring in this case we have selected A2.
2. Then we will enter the formula=LEFT(A2,3) in another cell.
3. The result will be the extraction of first part of the string.
This function is applicable for 2016\2019\mac\online versions.
How To Extract The End Of A substring Using RIGHT Function?
The method used for the extraction of the last part a substring is use of Right function. This function also consists of a formula, by applying this formula we will get the last part of substring. This technique works with 2016\2019\mac\online versions.
1. First we will select the cell having a substring i.e. A2.
2. Then we will type the formula=RIGHT(A2,3) in the other cell.
3. This will cause the separation of the last part of the selected substring.
This procedure is applicable for 2016\2019\mac\online versions.
How To Extract The Middle Part OFA Substring Using MID Function?
The middle part of a substring is extracted in the same manner like the first and the last part. The only difference is the change in formula. The formula used to separate middle segment of a substring is =MID(A2,3). This technique is applicable for 2016\2019\mac\online versions and results in extraction of middle part of a substring.
How To Find A Substring?
If you want to find a certain part of a substring in a data consisting of multiple substrings you can simply perform Search and Find function. For this method you will simply add the characters you want to find in a formula.
1. Suppose we need to find 2DZ from certain substrings IN A2.
2. We will apply formula =ISNUMBER(SEARCH(“2DZ”,A2).
3. If the cell consists this character then True will appear in the other cell otherwise False will be written in the other cell.
This technique is also works with 2016\2019\mac\online versions.
Did you learn about how to extract a substring in excel using different methods? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides.
You can also download WPS Office to edit the word documents, excel, and PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.
Was this helpful?
- 1. How to compare two excel sheets and highlight differences
- 2. How to import external data in WPS Spreadsheet
- 3. How to use the VLOOKUP function across multiple sheets in WPS Office Excel?
- 4. How to expand cells to fit text in Excel
- 5. How to insert a check box in WPS Spreadsheet
- 6. How to change date format in a cell