Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Create hyperlink with VLOOKUP in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel ROW Function
  • Last row number in range

Data Analysis Examples

  • How To Load Analysis ToolPak in Excel
  • Excel Line Chart
  • How to calculate correlation coefficient Correlation in Excel
  • Filter Data Based on Date in Excel
  • How to sum a total in multiple Excel tables

Data Validation Examples

  • How To Create Drop-down List in Excel
  • Excel Data validation whole percentage only
  • Excel Data validation unique values only
  • Excel Data validation specific characters only
  • Excel Data validation must not contain

How to extract nth word from text string in excel

by

If you need to get the nth word in a text string (i.e. a sentence, phrase, or paragraph) you can so with a clever (and intimidating) formula that combines 5 Excel functions: MID, SUBSTITUTE, TRIM,  REPT, and LEN.

 Formula

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

Explanation

How this formula works

At the core, this formula takes a text string with spaces, and “floods” it with additional spaces by replacing each space with a number of spaces using SUBSTITUTE and REPT. The number of spaces used is based on the overall length of the original text.

Worked Example:   TRIM function: Description, Usage, Syntax, Examples and Explanation

You can think of the result at this point as “islands” of words floating in a sea of space:)

Then the formula uses the MID function to extract the desired word. The starting point is worked out with:

Worked Example:   How to count line breaks in cell in Excel worksheet

(N-1)*LEN(A1)+1

And the total characters extracted is equal to the length of the full text string.

At this point, we have the word you want, surrounded by spaces. Finally, the TRIM function slices off all space characters and returns just the word.

I really like this formula, because it shows off what you can accomplish with some creative thinking.

Worked Example:   Extract word that begins with specific character in Excel

Text to Columns

Don’t forget that Excel has a built-in Text to Columns feature that can split text according to the delimiter of your choice. If you just need to get the 3rd word from a lot of text strings, the formula may be more convenient (and dynamic), but Text to Columns is still useful in many situations.

Post navigation

Previous Post:

How to extract multiple lines from a cell in Excel

Next Post:

How to extract substring in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to check if cell contains one of many things in Excel
  • Extract middle name from full name — Manipulating NAMES in Excel
  • Convert Numbers to Text in Excel
  • How to strip numeric characters from cell in Excel
  • Normalize text by removing punctuations, extra spaces and more in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • SECOND function: Description, Usage, Syntax, Examples and Explanation
  • YEARFRAC function: Description, Usage, Syntax, Examples and Explanation
  • Get last working day in month in Excel
  • Convert text to date in Excel
  • PMT, RATE, NPER, PV and FV Financial Functions in Excel
  • Calculate interest for given period in Excel
  • AMORLINC function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan principal payments in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles