How to get Substring Between 2 Delimiters

To get a substring between 2 delimiters, we need to determine the positions of the two spaces in the original string. We can achieve this by using the SEARCH Function and some additional steps:

• Use the SEARCH Function to find the position of the first space (” “) in the text. and then add 1 to get the ‘Start_num’ argument for the MID formula. This ensured that we start extracting the characters that follow the first space:

‘SEARCH(” “, A2) + 1’ .

• Find the position of the second space character by using nested SEARCH Functions. These nested functions instruct Excel to start searching for the second occurrence of the space character:

‘SEARCH(” “, A2)+1)’.

• To determine the number of characters to return, subtract the position of the first space from the position of the second space and then subtract 1 from the result. This ensures that we don’t include any extra space in the resulting substring:

SEARCH(” “,A2, SEARCH(” “,A2) +1) – SEARCH(” “,A2)’.

With all the arguments combined, here’s the Excel MID Formula to extract the substring between the two space characters(i.e., the middle name):

=MID(A2, SEARCH(” “,A2) + 1, SEARCH(” “,A2, SEARCH(” “,A2) +1) – SEARCH(” “,A2) – 1)

Using this formula will efficiently extract the middle name from cell A2, even if it is present in the first and last names.

Similarly, you can extract substring between any other delimiters by adjusting the formula accordingly:

=MID(string, SEARCH(delimiter, string) + 1, SEARCH(delimiter, string , SEARCH(delimiter, string) + 1) – SEARCH(delimiter, String) – 1)

If you use programming languages, like Python, you must have used a function called string slicing, which extracts a part of the string and can directly print it, or store a result. The same functionality can be achieved in Excel when we use Excel’s MID function.

The MID function can return a part of the string provided to it. Let us see the workings of this function.