How to extract a date (month) form a text string

Dear Experts,
I need a formula which allows me to extract the month out of a text string and have the month in date format and also in text format
This is the text string: Split of expenses for January, 2012 and I need just "January"
I need this for a second formula which shall act based on this month...
thanks
Nils
Petersburg1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Try this ARRAY formula for cell A1

=TEXT(DATE(1,MAX(IF(ISERROR(FIND({"january";"february";"march";"april";"may";"june";"july";"august";"september";"october";"november";"december"},LOWER(A1))),FALSE,ROW(A1:A12))),1),"mmmm")
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Petersburg1Author Commented:
Hi, thank you but I only get back December.....In Cell A I have the string and in Cell B I want to have the formula:
Cell A: "Split of expenses for January, 2012"
Cell B: "January"

thanks
Saqib Husain, SyedEngineerCommented:
Did you enter it as an array formula?

Select the formula cell
press F2
Press Ctrl-shift-enter
Petersburg1Author Commented:
Hi,
this formula e.g. (=MID(A1;23;7) does not fit as I have 12 such strings and the name of the month has a different length:
thanks

Split of expenses for January, 2012
Split of expenses for February, 2012
Split of expenses for March, 2012
Split of expenses for April, 2012
Split of expenses for May, 2012
Split of expenses for June, 2012
Split of expenses for July, 2012
Split of expenses for August, 2012
Split of expenses for September, 2012
Split of expenses for October, 2012
Split of expenses for November, 2012
Split of expenses for December, 2012
Saqib Husain, SyedEngineerCommented:
=SUBSTITUTE(A1,"Split of expenses for ","")
Saqib Husain, SyedEngineerCommented:
Or

=RIGHT(A1,LEN(A1)-22)
Saqib Husain, SyedEngineerCommented:
The first formula is useful if the rest of the text is not fixed.
Saqib Husain, SyedEngineerCommented:
For the month name only you can use

=MID(A1,23,LEN(A1)-28)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dlmilleCommented:
If your text string is always like:

Split of expenses for July, 2012

Then the formula becomes much simpler:

for the month as text (and the string is in B6):
=TEXT(LEFT(RIGHT(B6,LEN(B6)-LEN("Split of expenses for ")),LEN(RIGHT(B6,LEN(B6)-LEN("Split of expenses for ")))-6),"mmmm")

for the date:
=TEXT(LEFT(RIGHT(B6,LEN(B6)-LEN("Split of expenses for ")),LEN(RIGHT(B6,LEN(B6)-LEN("Split of expenses for ")))-6)  &" " & RIGHT(B6,4),"mmmm yyyy")

See attached.

Dave
stringToMonth-r1.xls
Rory ArchibaldCommented:
How is that "much simpler" than Saqibh's?
Rory ArchibaldCommented:
I was referring to:
=MID(A1,23,LEN(A1)-28)

Hmm, it appears that the comment I was replying to has disappeared!
dlmilleCommented:
I see that, now, lol - And a TEXT conversion with minor manipulation will get to the date.

Kudo's Saqibh!

Dave
Saqib Husain, SyedEngineerCommented:
I was just going to check out the meaning of "Simpler" in the dictionary just in case I am missing something ;-)
dlmilleCommented:
ROLF

;)
Rory ArchibaldCommented:
Who's Rolf? ;)
dlmilleCommented:
ROLF = "Rolling on the floor laughing"
Rory ArchibaldCommented:
That would be ROFL, no?
dlmilleCommented:
Now, I'm really ROFL.  I'm going to bed.  Good night, All!
Saqib Husain, SyedEngineerCommented:
Rolling on the "Laughing" floor
Rory ArchibaldCommented:
Hasta mañana. :)
dlmilleCommented:
Reminds me of "Song of the South" (a 70's movie I think) - going to my "laughing place"
Petersburg1Author Commented:
thank you
Nils
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.