Text and date formula

I have attached a spreadsheet that should explain what I am trying to do which is:

I want to concatenate text and date, but the date needs to be like 1st  Aug 16, 2nd, 3rd, 4th Aug 16 etc so not like 1/8/16, 1 Aug 16 or 1-8-16

So, Collections JK13 would become Collections BH13 24th Aug 16 after concatenation
dates-and-text.xlsx
JagwarmanAsked:
Who is Participating?
 
ProfessorJimJamConnect With a Mentor Commented:
put this formula in G7

=F7&" "&DAY(G2)&IF(OR(DAY(A2)={1,2,3,21,22,23,31}),
CHOOSE(1*RIGHT(DAY(G2),1),"st","nd ","rd "),"th")&
" "&TEXT(G2,"mmmm, yyyy")
dates-and-text.xlsx
0
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
@Professor
For some dates, date prefix is not working e.g. 21, 22,31 etc. Please revise the formula. :)

Or another formula could be this.....
In G7
=F7&" "&DAY(G2)&MID("thstndrdth",MIN(9,2*RIGHT(DAY(G2))*(ABS(MOD(DAY(G2),100)-12)>1)+1),2)&" "&TEXT(G2,"mmmm, yyyy")

Open in new window

0
 
JagwarmanAuthor Commented:
Superstar Thanks
1
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Jagwarman
I assume that compliment is for both of us. Right? :)
0
 
JagwarmanAuthor Commented:
Subodh Tiwari (Neeraj) they all work for me and so do yours

thanks
0
 
JagwarmanAuthor Commented:
yes brilliant both of you many thanks
0
 
JagwarmanAuthor Commented:
thanks guys
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad we could help.
0
 
ProfessorJimJamCommented:
thanks Jagwarman

thanks Neeraj :)
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.