Hello, I wondered if anyone could help me with the following query...I want
to read a section of text in a cell which is always the fifth word in, the
words before that are seperated by a comma and space. Thank you

Fiona difficult without seeing how the comma/spaces are arrabged but here's a
couple of ways:-

have a look at this function
http://www.j-walk.com/ss/excel/tips/tip32.htm

Or if your 5th word is surrounded by spaces you could use the very unweildy:-

=MID(A1,SEARCH(CHAR(127),SUBSTITUTE(A1,"
",CHAR(127),4))+1,SEARCH(CHAR(127),SUBSTITUTE( A1,"
",CHAR(127),5))-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),4))-1)

It looks for the 4th space which I have assumen immediatly precedes your 5th
word and axtracts all the text before the next space. This could just as
easily find a comma by changeing " " to ","



Mike
"Fiona" wrote:

Hello, I wondered if anyone could help me with the following query...I want
to read a section of text in a cell which is always the fifth word in, the
words before that are seperated by a comma and space. Thank you

Thanks Mike

"Mike H" wrote:

Fiona difficult without seeing how the comma/spaces are arrabged but here's a
couple of ways:-

have a look at this function
http://www.j-walk.com/ss/excel/tips/tip32.htm

Or if your 5th word is surrounded by spaces you could use the very unweildy:-

=MID(A1,SEARCH(CHAR(127),SUBSTITUTE(A1,"
",CHAR(127),4))+1,SEARCH(CHAR(127),SUBSTITUTE( A1,"
",CHAR(127),5))-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),4))-1)

It looks for the 4th space which I have assumen immediatly precedes your 5th
word and axtracts all the text before the next space. This could just as
easily find a comma by changeing " " to ","



Mike
"Fiona" wrote:

Hello, I wondered if anyone could help me with the following query...I want
to read a section of text in a cell which is always the fifth word in, the
words before that are seperated by a comma and space. Thank you

Hi Mike,I've had a go at the formula and followed your link which was very
useful, but I'm still a bit stuck! I have a list containing over 800 rows.
The list shows, for example, one, two, three, four, five then on the next
row, red, yellow, green, blue, purple

I want to read on the next page of my worksheet the fifth word of that
string, and copy it down so each row reads the fifth word whatever it is.
I've tried =ExtractElement("Sheet2!C3",5,", ") but it just shows #NAME? but
I'm not sure what I'm doing wrong.

Thanks again

"Mike H" wrote:

Fiona difficult without seeing how the comma/spaces are arrabged but here's a
couple of ways:-

have a look at this function
http://www.j-walk.com/ss/excel/tips/tip32.htm

Or if your 5th word is surrounded by spaces you could use the very unweildy:-

=MID(A1,SEARCH(CHAR(127),SUBSTITUTE(A1,"
",CHAR(127),4))+1,SEARCH(CHAR(127),SUBSTITUTE( A1,"
",CHAR(127),5))-SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),4))-1)

It looks for the 4th space which I have assumen immediatly precedes your 5th
word and axtracts all the text before the next space. This could just as
easily find a comma by changeing " " to ","



Mike
"Fiona" wrote:

Hello, I wondered if anyone could help me with the following query...I want
to read a section of text in a cell which is always the fifth word in, the
words before that are seperated by a comma and space. Thank you