Hi...

I want to extract text from a cell in much the same way as you would use the
left or right function. However, I want to just ignore the first two words in
the cell and copy to another cell the remaining text. For example, this is a
typical cell...

Summer Term St Thomas High School.

I want to remove the Summer Term and keep the St Thomas High School...

This problem is driving me nuts. Grateful for any help...

Gordon.

Gordon,

Try the following formula

=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,LEN(A1))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gordon" wrote in message
...
Hi...

I want to extract text from a cell in much the same way as you
would use the
left or right function. However, I want to just ignore the
first two words in
the cell and copy to another cell the remaining text. For
example, this is a
typical cell...

Summer Term St Thomas High School.

I want to remove the Summer Term and keep the St Thomas High
School...

This problem is driving me nuts. Grateful for any help...

Gordon.

If the full text is in A1, then =RIGHT(A1,LEN(A1)-FIND(" ",A1,1+FIND("
",A1))) will drop everything up through the second space character. So if
your words are separated by single spaces, that ought to do it.

"Gordon" wrote:

Hi...

I want to extract text from a cell in much the same way as you would use the
left or right function. However, I want to just ignore the first two words in
the cell and copy to another cell the remaining text. For example, this is a
typical cell...

Summer Term St Thomas High School.

I want to remove the Summer Term and keep the St Thomas High School...

This problem is driving me nuts. Grateful for any help...

Gordon.

Here it is as a formula...

=RIGHT(A1, LEN(A1)-FIND(" ", A1,FIND(" ",A1)+1))

Did you need it in code. If so it would be approximately the same thing only
with the instr function.
--
HTH...

Jim Thomlinson


"Gordon" wrote:

Hi...

I want to extract text from a cell in much the same way as you would use the
left or right function. However, I want to just ignore the first two words in
the cell and copy to another cell the remaining text. For example, this is a
typical cell...

Summer Term St Thomas High School.

I want to remove the Summer Term and keep the St Thomas High School...

This problem is driving me nuts. Grateful for any help...

Gordon.