Lesson 153: How to handle substrings in Excel

There is no SUBSTRING function in Excel. Use MID, LEFT, RIGHT, FIND, LEN, SUBSTITUTE, REPT, TRIM and MAX in Excel to extract substrings.

Join the channel Telegram of the AnonyViet 👉 Link 👈

How to handle substrings in Excel

Mid

To extract a substring, starting in the middle of a string, use the MID function in Excel.

Explanation: the MID function starts at position 7 (O) and extracts 6 characters.

Left

To extract the leftmost characters of a string, use the LEFT function in Excel.

To extract a substring (of any length) before the dash, add the FIND function.

Explanation: the FIND function finds the position of the dash. Subtract 1 from this result to extract the correct number of leftmost characters. The formula shown above will become LEFT(A1,4-1).

Right

To extract the rightmost characters of a string, use the RIGHT function in Excel.

To extract a substring (of any length) after the dash, add LEN and FIND.

Explanation: the LEN function returns the length of the string. The FIND function finds the position of the dash. Subtract these values to extract the correct number of rightmost characters. The formula shown above will become RIGHT (A1,6-4).

Find substring between parentheses

To extract a substring between parentheses (or curly braces, curly braces, slashes, etc.), use Excel’s MID and FIND.

1. The recipe below is almost perfect.

Explanation: the FIND function finds the position of the brackets. Add 1 to find the starting position of the substring. The formula shown above will become MID (A1,6 + 1,2). This MID function always extracts 2 characters.

2. Replace parameter 3 with a formula that returns the length of the substring.

Explanation: subtract the position of the opening brace and the value 1 from the position of the closing brace to find the exact length of the substring.

Substring containing specific text

To extract a substring containing specific text (e.g. @ symbol), use SUBSTITUTE, REPT, MID, FIND, TRIM, and MAX in Excel.

1. First, use SUBSTITUTE and REPT to replace a single space with 100 spaces (or any other large number).

2. The MID function below starts position 50 (1/2 * large number) before the position of the @ symbol and extracts 100 (large number) characters.

3. Use the TRIM function to remove leading and trailing whitespace.

4. Put it all together.

Note: in step 2, the MID function starts from position 50 before the @ symbol. If the email address is the first word in the sentence (cell A3), this results in the wrong starting position. In this case, the MAX function (see formula above) returns 1.