In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form of
ddmmyy.
how can i get date and month r in cell A2 & A3 from this.

e.g. A1 A2
A3
NW/RON/ENT/05/010209 01 FEB 09 FEB
NW/SR-B/ENT/04/030509 03 MAY 09 MAY

Hi

I am assuming that you really mean you want the Date in B1 and the Month in
C1.
In B1 enter
=RIGHT(A1,6)
Copy down as far as required
Copy column BPaste SpecialValues
Select column BDataText to ColumnsNextNextDateD/M/YFinish

In cell C1
=TEXT(B1,"mmm")
Copy down as required

--
Regards
Roger Govier

"aditya" wrote in message
...
In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form
of
ddmmyy.
how can i get date and month r in cell A2 & A3 from this.

e.g. A1 A2
A3
NW/RON/ENT/05/010209 01 FEB 09 FEB
NW/SR-B/ENT/04/030509 03 MAY 09 MAY

On Tue, 2 Jun 2009 23:54:01 -0700, aditya
wrote:

In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form of
ddmmyy.
how can i get date and month r in cell A2 & A3 from this.

e.g. A1 A2
A3
NW/RON/ENT/05/010209 01 FEB 09 FEB
NW/SR-B/ENT/04/030509 03 MAY 09 MAY

To extract the last six characters and change it into a date, you can use this
formula:

=DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2))

You can use that formula in A2 and A3 and custom format according to your
requirements, or you can wrap a TEXT function around the formula to give a
similar display (although one that you may not be able to use in any
calculations.

So, using the TEXT function method:

A2:

=TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"dd mmm yy")

A3:

=TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"mmm")

--ron

Or if you have your Windows Regional Options set to recognisedates as
ddmmyy, you can simplify Ron's A2 formula to
=--TEXT(RIGHT(A1,6),"00\-00\-00") and format as date, and A3 can (in either
case) be =TEXT(A2,"mmm").
You may need to use UPPER() if you insist on upper case.
--
David Biddulph

"Ron Rosenfeld" wrote in message
...
On Tue, 2 Jun 2009 23:54:01 -0700, aditya

wrote:

In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form
of
ddmmyy.
how can i get date and month r in cell A2 & A3 from this.

e.g. A1 A2
A3
NW/RON/ENT/05/010209 01 FEB 09 FEB
NW/SR-B/ENT/04/030509 03 MAY 09 MAY

To extract the last six characters and change it into a date, you can use
this
formula:

=DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2))

You can use that formula in A2 and A3 and custom format according to your
requirements, or you can wrap a TEXT function around the formula to give a
similar display (although one that you may not be able to use in any
calculations.

So, using the TEXT function method:

A2:

=TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"dd mmm yy")

A3:

=TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"mmm")

--ron

On Wed, 3 Jun 2009 11:09:41 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Or if you have your Windows Regional Options set to recognisedates as
ddmmyy, you can simplify Ron's A2 formula to
=--TEXT(RIGHT(A1,6),"00\-00\-00") and format as date, and A3 can (in either
case) be =TEXT(A2,"mmm").
You may need to use UPPER() if you insist on upper case.
--
David Biddulph

Of course, that solution is critically dependent on the Windows Regional
settings. It won't work, for example, with standard US settings. The solution
I offered should work no matter how the Windows Regional settings are set.
--ron

On Wed, 03 Jun 2009 05:38:49 -0400, Ron Rosenfeld
wrote:

On Tue, 2 Jun 2009 23:54:01 -0700, aditya
wrote:

In cell A1, data is of 19 or 20 or 21 digit whose last 6 digit is in form of
ddmmyy.
how can i get date and month r in cell A2 & A3 from this.

e.g. A1 A2
A3
NW/RON/ENT/05/010209 01 FEB 09 FEB
NW/SR-B/ENT/04/030509 03 MAY 09 MAY

To extract the last six characters and change it into a date, you can use this
formula:

=DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2))

You can use that formula in A2 and A3 and custom format according to your
requirements, or you can wrap a TEXT function around the formula to give a
similar display (although one that you may not be able to use in any
calculations.

So, using the TEXT function method:

A2:

=TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1, "/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"dd mmm yy")

A3:

=TEXT(DATE(1900+RIGHT(TRIM(RIGHT(SUBSTITUTE(A1, "/",REPT(" ",99)),99)),2)
+100*(--RIGHT(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)<30),
MID(TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),3,2),LEFT(TRIM(
RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99)),2)),"mmm")

--ron

Stupid me.

Much simpler:

For the date:

=DATE(RIGHT(A1,2)+1900+100*(--RIGHT(A1,2)<30),
LEFT(RIGHT(A1,4),2),LEFT(RIGHT(A1,6),2))

and for the formatted date:

=TEXT(DATE(RIGHT(A1,2)+1900+100*(--RIGHT(A1,2)<30),
LEFT(RIGHT(A1,4),2),LEFT(RIGHT(A1,6),2)),"dd mmm yy")

and for the formatted month:

=TEXT(DATE(RIGHT(A1,2)+1900+100*(--RIGHT(A1,2)<30),
LEFT(RIGHT(A1,4),2),LEFT(RIGHT(A1,6),2)),"mmm")

--ron

Agreed. Hence the first clause of my reply.
--
David Biddulph

"Ron Rosenfeld" wrote in message
...
On Wed, 3 Jun 2009 11:09:41 +0100, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Or if you have your Windows Regional Options set to recognisedates as
ddmmyy, you can simplify Ron's A2 formula to
=--TEXT(RIGHT(A1,6),"00\-00\-00") and format as date, and A3 can (in
either
case) be =TEXT(A2,"mmm").
You may need to use UPPER() if you insist on upper case.
--
David Biddulph

Of course, that solution is critically dependent on the Windows Regional
settings. It won't work, for example, with standard US settings. The
solution
I offered should work no matter how the Windows Regional settings are set.
--ron