Hi,

Cells in Column A have strings of the following type:
eg A1:
Billing for the period March 14 2009 to March 13 2010 - Order dated
April 01 2009
In B1 I want the date : 14-Mar-2009
In C1 I want the date: 13-Mar-2010
(to elaborate, only the first two dates in the string need to be
extracted, third and subsequent dates in the string to be ignored)

Thanks in advance for the help.

Regards,
Raj

Try for start date:
=MID(A1,FIND("period",A1)+7,(FIND("to",A1))-(FIND("period",A1)+8))

Stop date:
=MID(A1,FIND("to",A1)+3,(FIND("-",A1))-(FIND("to",A1)+4))


HTH


"Raj" wrote:

Hi,

Cells in Column A have strings of the following type:
eg A1:
Billing for the period March 14 2009 to March 13 2010 - Order dated
April 01 2009
In B1 I want the date : 14-Mar-2009
In C1 I want the date: 13-Mar-2010
(to elaborate, only the first two dates in the string need to be
extracted, third and subsequent dates in the string to be ignored)

Thanks in advance for the help.

Regards,
Raj

On Oct 7, 9:03*pm, Al wrote:
Try for start date:
=MID(A1,FIND("period",A1)+7,(FIND("to",A1))-(FIND("period",A1)+8))

Stop date:
=MID(A1,FIND("to",A1)+3,(FIND("-",A1))-(FIND("to",A1)+4))

HTH



"Raj" wrote:
Hi,

Cells in Column A have strings of the following type:
eg A1:
Billing for the period March 14 2009 to March 13 2010 - Order dated
April 01 2009
In B1 I want the date : 14-Mar-2009
In C1 I want the date: *13-Mar-2010
(to elaborate, only the first two dates in the string need to be
extracted, third and subsequent dates in the string to be ignored)

Thanks in advance for the help.

Regards,
Raj- Hide quoted text -

- Show quoted text -

Thanks. While that worked right for the given string, it did not work
for all the rows as the strings are composed differently. The word
"period" does not precede the first date at all times nor does the "-"
suceed the second date. Is there a way to extract the first two dates
by looking for the first and second occurrences of the standard "mmmm"
months in the String and then compute the dates based on the position
of those strings?

Regards
Raj

I think you need to give us a little more information about the structure of
these text strings of yours. Is the month name **always** spelled out? Is
the year **always** a 4-digit number? Is the only character between date
parts the space character (that is, a comma is **never** used between the
day and the year)? Is the word "to" **always** used to separate the two
dates and, if not, can the text separating them be more than one word long?
Could there other numbers before the first date?
--
Rick (MVP - Excel)


"Rajendra" wrote in message
...
On Oct 7, 9:03 pm, Al wrote:
Try for start date:
=MID(A1,FIND("period",A1)+7,(FIND("to",A1))-(FIND("period",A1)+8))

Stop date:
=MID(A1,FIND("to",A1)+3,(FIND("-",A1))-(FIND("to",A1)+4))

HTH



"Raj" wrote:
Hi,

Cells in Column A have strings of the following type:
eg A1:
Billing for the period March 14 2009 to March 13 2010 - Order dated
April 01 2009
In B1 I want the date : 14-Mar-2009
In C1 I want the date: 13-Mar-2010
(to elaborate, only the first two dates in the string need to be
extracted, third and subsequent dates in the string to be ignored)

Thanks in advance for the help.

Regards,
Raj- Hide quoted text -

- Show quoted text -

Thanks. While that worked right for the given string, it did not work
for all the rows as the strings are composed differently. The word
"period" does not precede the first date at all times nor does the "-"
suceed the second date. Is there a way to extract the first two dates
by looking for the first and second occurrences of the standard "mmmm"
months in the String and then compute the dates based on the position
of those strings?

Regards
Raj

On Oct 7, 11:22 pm, "Rick Rothstein"
wrote:
I think you need to give us a little more information about the structure of
these text strings of yours. Is the month name **always** spelled out? Is
the year **always** a 4-digit number? Is the only character between date
parts the space character (that is, a comma is **never** used between the
day and the year)? Is the word "to" **always** used to separate the two
dates and, if not, can the text separating them be more than one word long?
Could there other numbers before the first date?
--
Rick (MVP - Excel)

"Rajendra" wrote in message

...
On Oct 7, 9:03 pm, Al wrote:



Try for start date:
=MID(A1,FIND("period",A1)+7,(FIND("to",A1))-(FIND("period",A1)+8))

Stop date:
=MID(A1,FIND("to",A1)+3,(FIND("-",A1))-(FIND("to",A1)+4))

HTH

"Raj" wrote:
Hi,

Cells in Column A have strings of the following type:
eg A1:
Billing for the period March 14 2009 to March 13 2010 - Order dated
April 01 2009
In B1 I want the date : 14-Mar-2009
In C1 I want the date: 13-Mar-2010
(to elaborate, only the first two dates in the string need to be
extracted, third and subsequent dates in the string to be ignored)

Thanks in advance for the help.

Regards,
Raj- Hide quoted text -

- Show quoted text -

Thanks. While that worked right for the given string, it did not work
for all the rows as the strings are composed differently. The word
"period" does not precede the first date at all times nor does the "-"
suceed the second date. Is there a way to extract the first two dates
by looking for the first and second occurrences of the standard "mmmm"
months in the String and then compute the dates based on the position
of those strings?

Regards
Raj

The month name is almost always spelled out in full : January,
February and so on. There are are a few instances of Jan or Feb in the
data.
Year is always a 4 digit number
The separator between dates is always either "to" or "To" or
"tp" (misspelling)
There are no numbers before the first date.
The dates are of the type: (actually more than I initially thought):
November 24th 2008 To May 23rd 2009
12th November 2008 To 11th November 2009
June 20 2009 to June 19 2010
1st Dec 08 tp 31st Dec 08

Thanks and Regards,
Raj

The month name is almost always spelled out in full : January,
February and so on. There are are a few instances of Jan or Feb in the
data.
Year is always a 4 digit number
The separator between dates is always either "to" or "To" or
"tp" (misspelling)
There are no numbers before the first date.
The dates are of the type: (actually more than I initially thought):
November 24th 2008 To May 23rd 2009
12th November 2008 To 11th November 2009
June 20 2009 to June 19 2010
1st Dec 08 tp 31st Dec 08

Your say in your 2nd answer that the year is always a 4-digit number, but
then in your last line you show dates with 2-digit years... do we have to
account for 2-digit years or not?

--
Rick (MVP - Excel)

See if the following macro does what you want. I assumed the starting row
for your data was Row 2 and that the column containing your text data is
Column A (you can change each of these as necessary in the obvious Const
statements); also, the split out dates are placed in the next two columns to
the right of the specified column containing your text data.

Sub SplitOutDates()
Dim X As Long, Z As Long, LastRow As Long
Dim FirstDate As Date, SecondDate As Date
Dim Txt As String, Parts() As String
Const StartRow = 2
Const DataCol = "A"
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
For X = StartRow To LastRow
Txt = Cells(X, DataCol)
Txt = Replace(Txt, "tp", "to", 1, -1, Compa=vbTextCompare)
Txt = Replace(Txt, "st", "", 1, -1, vbTextCompare)
Txt = Replace(Txt, "nd", "", 1, -1, vbTextCompare)
Txt = Replace(Txt, "rd", "", 1, -1, vbTextCompare)
Txt = Replace(Txt, "th", "", 1, -1, vbTextCompare)
Parts = Split(" " & Txt & " ", " to ", -1, vbTextCompare)
On Error GoTo Continue
For Z = 0 To UBound(Parts) - 1
If UBound(Split(Parts(Z))) 2 Then
FirstDate = Split(WorksheetFunction.Substitute(Parts(Z), " ", _
Chr$(1), UBound(Split(Parts(Z))) - 2), Chr$(1))(1)
SecondDate = Split(WorksheetFunction.Substitute(Parts(Z + 1), _
" ", Chr$(1), 3), Chr$(1))(0)
Exit For
End If
Continue:
Next
Cells(X, DataCol).Offset(0, 1).Value = FirstDate
Cells(X, DataCol).Offset(0, 2).Value = SecondDate
Next
End Sub

--
Rick (MVP - Excel)


"Rajendra" wrote in message
...
On Oct 7, 11:22 pm, "Rick Rothstein"
wrote:
I think you need to give us a little more information about the structure
of
these text strings of yours. Is the month name **always** spelled out? Is
the year **always** a 4-digit number? Is the only character between date
parts the space character (that is, a comma is **never** used between the
day and the year)? Is the word "to" **always** used to separate the two
dates and, if not, can the text separating them be more than one word
long?
Could there other numbers before the first date?
--
Rick (MVP - Excel)

"Rajendra" wrote in message

...
On Oct 7, 9:03 pm, Al wrote:



Try for start date:
=MID(A1,FIND("period",A1)+7,(FIND("to",A1))-(FIND("period",A1)+8))

Stop date:
=MID(A1,FIND("to",A1)+3,(FIND("-",A1))-(FIND("to",A1)+4))

HTH

"Raj" wrote:
Hi,

Cells in Column A have strings of the following type:
eg A1:
Billing for the period March 14 2009 to March 13 2010 - Order dated
April 01 2009
In B1 I want the date : 14-Mar-2009
In C1 I want the date: 13-Mar-2010
(to elaborate, only the first two dates in the string need to be
extracted, third and subsequent dates in the string to be ignored)

Thanks in advance for the help.

Regards,
Raj- Hide quoted text -

- Show quoted text -

Thanks. While that worked right for the given string, it did not work
for all the rows as the strings are composed differently. The word
"period" does not precede the first date at all times nor does the "-"
suceed the second date. Is there a way to extract the first two dates
by looking for the first and second occurrences of the standard "mmmm"
months in the String and then compute the dates based on the position
of those strings?

Regards
Raj

The month name is almost always spelled out in full : January,
February and so on. There are are a few instances of Jan or Feb in the
data.
Year is always a 4 digit number
The separator between dates is always either "to" or "To" or
"tp" (misspelling)
There are no numbers before the first date.
The dates are of the type: (actually more than I initially thought):
November 24th 2008 To May 23rd 2009
12th November 2008 To 11th November 2009
June 20 2009 to June 19 2010
1st Dec 08 tp 31st Dec 08

Thanks and Regards,
Raj

On Oct 8, 2:13 am, "Rick Rothstein"
wrote:
See if the following macro does what you want. I assumed the starting row
for your data was Row 2 and that the column containing your text data is
Column A (you can change each of these as necessary in the obvious Const
statements); also, the split out dates are placed in the next two columns to
the right of the specified column containing your text data.

Sub SplitOutDates()
Dim X As Long, Z As Long, LastRow As Long
Dim FirstDate As Date, SecondDate As Date
Dim Txt As String, Parts() As String
Const StartRow = 2
Const DataCol = "A"
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
For X = StartRow To LastRow
Txt = Cells(X, DataCol)
Txt = Replace(Txt, "tp", "to", 1, -1, Compa=vbTextCompare)
Txt = Replace(Txt, "st", "", 1, -1, vbTextCompare)
Txt = Replace(Txt, "nd", "", 1, -1, vbTextCompare)
Txt = Replace(Txt, "rd", "", 1, -1, vbTextCompare)
Txt = Replace(Txt, "th", "", 1, -1, vbTextCompare)
Parts = Split(" " & Txt & " ", " to ", -1, vbTextCompare)
On Error GoTo Continue
For Z = 0 To UBound(Parts) - 1
If UBound(Split(Parts(Z))) 2 Then
FirstDate = Split(WorksheetFunction.Substitute(Parts(Z), " ", _
Chr$(1), UBound(Split(Parts(Z))) - 2), Chr$(1))(1)
SecondDate = Split(WorksheetFunction.Substitute(Parts(Z + 1), _
" ", Chr$(1), 3), Chr$(1))(0)
Exit For
End If
Continue:
Next
Cells(X, DataCol).Offset(0, 1).Value = FirstDate
Cells(X, DataCol).Offset(0, 2).Value = SecondDate
Next
End Sub

--
Rick (MVP - Excel)

"Rajendra" wrote in message

...

On Oct 7, 11:22 pm, "Rick Rothstein"
wrote:
I think you need to give us a little more information about the structure
of
these text strings of yours. Is the month name **always** spelled out? Is
the year **always** a 4-digit number? Is the only character between date
parts the space character (that is, a comma is **never** used between the
day and the year)? Is the word "to" **always** used to separate the two
dates and, if not, can the text separating them be more than one word
long?
Could there other numbers before the first date?
--
Rick (MVP - Excel)

"Rajendra" wrote in message

...
On Oct 7, 9:03 pm, Al wrote:

Try for start date:
=MID(A1,FIND("period",A1)+7,(FIND("to",A1))-(FIND("period",A1)+8))

Stop date:
=MID(A1,FIND("to",A1)+3,(FIND("-",A1))-(FIND("to",A1)+4))

HTH

"Raj" wrote:
Hi,

Cells in Column A have strings of the following type:
eg A1:
Billing for the period March 14 2009 to March 13 2010 - Order dated
April 01 2009
In B1 I want the date : 14-Mar-2009
In C1 I want the date: 13-Mar-2010
(to elaborate, only the first two dates in the string need to be
extracted, third and subsequent dates in the string to be ignored)

Thanks in advance for the help.

Regards,
Raj- Hide quoted text -

- Show quoted text -

Thanks. While that worked right for the given string, it did not work
for all the rows as the strings are composed differently. The word
"period" does not precede the first date at all times nor does the "-"
suceed the second date. Is there a way to extract the first two dates
by looking for the first and second occurrences of the standard "mmmm"
months in the String and then compute the dates based on the position
of those strings?

Regards
Raj

The month name is almost always spelled out in full : January,
February and so on. There are are a few instances of Jan or Feb in the
data.
Year is always a 4 digit number
The separator between dates is always either "to" or "To" or
"tp" (misspelling)
There are no numbers before the first date.
The dates are of the type: (actually more than I initially thought):
November 24th 2008 To May 23rd 2009
12th November 2008 To 11th November 2009
June 20 2009 to June 19 2010
1st Dec 08 tp 31st Dec 08

Thanks and Regards,
Raj

Rick,

It worked like magic. Thanks a Ton

Regards,
Raj