Breaking text up into words in excel formula – 3rd,4th etc

These formulas work well and are easy to adapt for any word in a string of text  – just change the SUBSTITUTE number through out the fomula to 1 less than the word you are looking for. The IFERROR returns a blank but it can be useful to return a character something like “^” instead for lookups.

4th
=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,3),FIND(“^”,SUBSTITUTE(A23,” “,”^”,3))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,3),FIND(“^”,SUBSTITUTE(A23,” “,”^”,3))+1,256)))),””)

3rd
=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,2),FIND(“^”,SUBSTITUTE(A23,” “,”^”,2))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,2),FIND(“^”,SUBSTITUTE(A23,” “,”^”,2))+1,256)))),””)

2nd
=IFERROR(TRIM(MID(MID(SUBSTITUTE(A23,” “,”^”,1),FIND(“^”,SUBSTITUTE(A23,” “,”^”,1))+1,256),1,FIND(” “,MID(SUBSTITUTE(A23&” “,” “,”^”,1),FIND(“^”,SUBSTITUTE(A23,” “,”^”,1))+1,256)))),””)

1st
=TRIM(MID(A23,1,FIND(” “,A23&” “)))

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s