Extract Nth word from text string
In the below example, we will explore an inventive use of the MID formula in Excel, combined with several other functions, to extract the Nth word from a text string. The approach involves using the LEN, REPT, SUBSTITUTE, MID, and TRIM Functions to achieve the desired result.
The generic formula to extract the Nth word from a given text string is as follows:
TRIN(MID(SUBSTITUTE(string, ” ‘, REPT(” “,LEN(string))), (n-1) * LEN(string) + 1, LEN(string)))
‘String’ is the original text string from which you want to extract the desired word.
‘N’ is the number of words to be executed.
Pull a word containing a specific character(s)
Below is the formula that shows another non-trivial Excel Mid formula that pulla a word containing a specific character(s) from anywhere in the original text string:
TRIM(MID(SUBSTITUTE(string,” “,REPT(” “,99)) ,MAX(1,FIND(char, SUBSTITUTE(string, ” “,REPT(” “,99)))-50),99))
Excel MID Function
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.
Contact Us