How to find the nth word in a excel cell

=IFERROR(IF($R$1=1,MID(O2,1,FIND(" ",O2,1)),MID(MID(MID(SUBSTITUTE(O2," ","^",$R$1),1,256),FIND("^",SUBSTITUTE(O2," ","^",$R$1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(O2," ","^",$R$1),1,256),FIND("^",SUBSTITUTE(O2," ","^",$R$1)),256))-2)),RIGHT(O2,LEN(O2)-FIND("*",SUBSTITUTE(O2," ","*",LEN(O2)-LEN(SUBSTITUTE(O2," ",""))))) )

This will give you what ever word you want in the cell range("O2"). You can tell excel what word placement you want returned in cell range("R1"). The one catch is that if you want to return the 3rd word in a cell you will need range("R1") to be 2. This is because excel starts counting with 0. So to find the 3rd word you would have to have "word 0" then "word 1" then "word 2". See how "word 2" is the 3rd word in the cell.

Good luck.

How to return the number of words in a cell?

Answer: the following formula will return the number of words in an excel cell.

 =If(LEN(TRIM(B1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(B1," ",""))+1)

Possible situation where you would want to use this formula: I download students data because I'm a DBA at xyc district. One column of data gives me the grades of each school (k 1 2 3 4 5). The next column gives me total enrollment. I want to find the average enrollment in each grade at each school. I would use the above formula to find the number of grades in each school so I could divide enrollment by number of grades at each school.