Excel Substring Functions: Learn How to Use Them

A substring refers to a smaller string located within a larger string. Specifically, it is a string of characters at the left, right, or middle of the data. To extract an Excel substring, we can use some text-based standard formulas with fixed and variable length. We can also use some non-formula-based techniques. 

But let’s go back to the basics.

We store different types of data in cells in Excel. This data can be a number, text, date, or something else like a URL. The data itself is called string. A substring is a part of the overall data string. 

Excel Substring Functions

If you’re ever asked about an Excel substring function during an Excel interview, it’s a trick question; there isn’t one. But though there are no specific substring functions in Excel, there are still ways to extract a substring from a larger string of data. 

If we have to extract a left, right, or middle substring from a longer string of data, especially where there are thousands of records, we can use LEFT, RIGHT, and MID functions of Excel. But this is only the beginning. Combining these functions with LEN and FIND functions, we can apply almost any kind of intelligence and extract any data that we need. We can also use tools such as Flash Fill, Find and Replace, and Text to Columns.

Recommended Learning Platform course

Excel for Accounting & Finance: Fundamentals Masterclass

Irfan Sharif ACA ACCA | #1 Best Selling Instructor of Accounting & Bookkeeping