Excel Formulas to Extract Nth Word From a Text String ~ Simple Tips!!

Have you ever suffered from the problem that you need to extract a certain word from the text string in a worksheet? Are there any good ways to deal with this problem quickly in Excel? Here, we are going to extract the Nth word from a text string in Excel. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

General Formula:

  • Use the below formula to extract the Nth word from a text string in Excel.

=TRIM(MID(SUBSTITUTE(A1,” “,REPT(” “,LEN(A1))), (N-1)*LEN(A1)+1, LEN(A1)))

Syntax Explanations:

  • TRIM – It helps to remove the extra spaces from the text and there is no space at the start or end of the text string. Read more on the TRIM function.
  • REPT – In Excel, the REPT function is used to repeat characters a given number of times.
  • MID – This function helps to extract the number (starting from the left side) or characters from the given string. Read more on the MID function.
  • FIND – The Excel, FIND function is used to find out the one text string inside the other.
  • LEN – In Excel, the LEN function is used to find the length of the text string.
  • SUBSTITUTE – This function helps to replace existing text with new text in a text string when you want to replace text based on its content, not position. Read more on the SUBSTITUTE function.
  • N – It specifies the Nth number of text.
  • A1 – It represents the input value.
  • Comma symbol (,) – It is a separator that helps to separate a list of values.
  • Parenthesis () – The main purpose of this symbol is to group the elements.
  • *Multiplication (*) *– In this symbol will multiply any two values or numbers.
  • Minus Operator (-) – This symbol will help to subtract any two values.
  • Plus operator (+) – This symbol is used to add the values.

Practical Example:

Let’s consider the below example.

  • First, we will enter the input text strings in Column B.
  • Here, we have to extract the Nth words from a text string.
  • Select any cell and type the above-given formula.
  • Finally, press the ** ENTER** key, you can get the result as shown below.

Bottom-Line:

From this article, you can get some clarification on how to extract the Nth words from a text string in Excel. Leave a comment or reply below to let me know what you think!

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **

Read Ahead:

17

Tags:

ExcelformulaExcelformulas