Return nth value from a string

This tutorial shows how to get the nth value from a string using an Excel formula, with the TRIM, MID, SUBSTITUTE, REPT and LEN functions

EXCEL FORMULA 1. Return nth value from a string

EXCEL

Hard coded formula
Return nth value from a string
Cell reference formula
Return nth value from a string
=TRIM(MID(SUBSTITUTE("Bread Milk Apples Cream"," ",REPT(" ",LEN("Bread Milk Apples Cream"))),(3-1)*LEN("Bread Milk Apples Cream")+1,LEN("Bread Milk Apples Cream")))
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))),(C5-1)*LEN(B5)+1,LEN(B5)))
GENERIC FORMULA

=TRIM(MID(SUBSTITUTE(string," ",REPT(" ",LEN(string))),(nth_value-1)*LEN(string)+1,LEN(string)))

ARGUMENTS
string: The string from which you want to return an nth value.
nth_value: The nth value to be returned from the string.

GENERIC FORMULA

=TRIM(MID(SUBSTITUTE(string," ",REPT(" ",LEN(string))),(nth_value-1)*LEN(string)+1,LEN(string)))

ARGUMENTS
string: The string from which you want to return an nth value.
nth_value: The nth value to be returned from the string.

EXPLANATION

This formula uses the TRIM, MID, SUBSTITUTE, REPT and LEN functions to get the nth value from a string.

Click on either the Hard Coded or Cell Reference button to view the formula that has string and the nth value to be return directly entered into the formula or referenced to specific cells.

RELATED TOPICS

Related Topic Description Related Topic and Description
Return nth character How to extract the nth character from a string

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
TRIM Function The Excel TRIM function removes the spaces at the start and end of the text and also removes the unnecessary spaces between words, leaving only a single space between words and numbers that form part of the text
MID Function The Excel MID function returns the specified number of characters from a selected string, starting at a specified position
LEN Function The Excel LEN function returns the number of characters in a specified string