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?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
For the month name only you can use

=MID(A1,23,LEN(A1)-28)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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")
0
 
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
0
 
Saqib Husain, SyedEngineerCommented:
Did you enter it as an array formula?

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

=RIGHT(A1,LEN(A1)-22)
0
 
Saqib Husain, SyedEngineerCommented:
The first formula is useful if the rest of the text is not fixed.
0
 
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
0
 
Rory ArchibaldCommented:
How is that "much simpler" than Saqibh's?
0
 
Rory ArchibaldCommented:
I was referring to:
=MID(A1,23,LEN(A1)-28)

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

Kudo's Saqibh!

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

;)
0
 
Rory ArchibaldCommented:
Who's Rolf? ;)
0
 
dlmilleCommented:
ROLF = "Rolling on the floor laughing"
0
 
Rory ArchibaldCommented:
That would be ROFL, no?
0
 
dlmilleCommented:
Now, I'm really ROFL.  I'm going to bed.  Good night, All!
0
 
Saqib Husain, SyedEngineerCommented:
Rolling on the "Laughing" floor
0
 
Rory ArchibaldCommented:
Hasta maƱana. :)
0
 
dlmilleCommented:
Reminds me of "Song of the South" (a 70's movie I think) - going to my "laughing place"
0
 
Petersburg1Author Commented:
thank you
Nils
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.