Left, Mid functions

  • Thread starter Thread starter Manos
  • Start date Start date
M

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
 
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.
 
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
Click to expand...

To extract only the "month", try:

=MID(TRIM(A1),SEARCH(" ",TRIM(A1))+1,SEARCH(" ",TRIM(A1),
SEARCH(" ",TRIM(A1))+1)-SEARCH(" ",TRIM(A1)))
 
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
 
Back
Top