# 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
###### Who is Participating?

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.

Commented:
Commented:
EngineerCommented:
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")
Author 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
EngineerCommented:
Did you enter it as an array formula?

Select the formula cell
press F2
Press Ctrl-shift-enter
Author 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
EngineerCommented:
=SUBSTITUTE(A1,"Split of expenses for ","")
EngineerCommented:
Or

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

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

Experts Exchange Solution brought to you by

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

Commented:
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
Commented:
How is that "much simpler" than Saqibh's?
Commented:
I was referring to:
=MID(A1,23,LEN(A1)-28)

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

Kudo's Saqibh!

Dave
EngineerCommented:
I was just going to check out the meaning of "Simpler" in the dictionary just in case I am missing something ;-)
Commented:
ROLF

;)
Commented:
Who's Rolf? ;)
Commented:
ROLF = "Rolling on the floor laughing"
Commented:
That would be ROFL, no?
Commented:
Now, I'm really ROFL.  I'm going to bed.  Good night, All!
EngineerCommented:
Rolling on the "Laughing" floor
Commented:
Hasta mañana. :)
Commented:
Reminds me of "Song of the South" (a 70's movie I think) - going to my "laughing place"
Author 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.