Left, Mid functions

[Manos]

Dear all

I have in a cell A1 the following " Y.T.D. APRIL 2004"

i want in a cell to bring only the Y.T.D.
I manage taht by using the formula with LEFT function

Now i want to bring in a cel only the name of the month.
APRIL

I try the formula mid but i did not had good result,
because if i have the month to Decemeber the formula
brings me the Decemeber. If i change the month to MAY
then it tooks the 2004 (less characters)

Any good ideas or greate comparison of formulas to
have only the month?

Thanks in advance
Manos

 

[Norman Harker]

Hi Manos!

Assuming you don't have the space at the beginning of your string:

=LEFT(MID(A1,FIND(" ",A1)+1,255),FIND(" ",MID(A1,FIND("
",A1)+1,255))-1)

If you do have that space at the beginning then:

=LEFT(MID(TRIM(A1),FIND(" ",TRIM(A1))+1,255),FIND("
",MID(TRIM(A1),FIND(" ",TRIM(A1))+1,255))-1)

But since you are using formulas to strip out several parts of the
string, you might be better off using Data > Text to Columns.

 

[Max]

I have in a cell A1 the following " Y.T.D. APRIL 2004"
...

Now i want to bring in a cel only the name of the month.
APRIL

To extract only the "month", try:

=MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,SEARCH(" ",TRIM(A1),
SEARCH(" ",TRIM(A1))+1)-SEARCH(" ",TRIM(A1)))

 

[Manos]

Thank you Norman

It works Perfectly

 

[Paul Simon]

Provided that there is always a space after Y.T.D. and that the first
word after that first space is always the month, here's one formula
that will work:

Assuming "Y.T.D. April 2004" is in cell A1

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

I hope this helps.
Paul