Hi,
I did have one more question and I wasn't sure since I had rated it if it
would come up again. When I used the formula on these examples

(BOND-NO NEW BONDS) Filed Chapter 11 Bankruptcy 9/15/09
It pulled out in the first column-- 11 and not the expected 9/15/09
and
(PERMANENT EXCEPTION) 3/09 10/05/09 stmt
It pulled out 3/09 instead of the expected 10/05/09

Is there a way to specify only to pull out the date if it is in a date
format 10/27/09?
Thanks Donna

"Ashish Mathur" wrote:

Click to show or hide original message or reply text.


You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Donna" wrote in message
...
Thank you veerrry much!! It does exactly what I need it to do!!
Donna
"Ashish Mathur" wrote:

Hi,

Assume the sentence is in cell A3. To get the date, use this formula in
B3

=TRIM(MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&" 0123456789")),SEARCH("
",A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"01234567 89")))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")) ))

To get everything before the date, use this formula in cell C3

=TRIM(LEFT(A3,SEARCH(B3,A3)-1))

To get everything after the date, use

=TRIM(RIGHT(A3,LEN(A3)-LEN(B3)-LEN(C3)-1))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Donna" wrote in message
...
How can I pull out the 1st date listed in the beginning of a cell of
text.
(new entrys are put at the beginning of the cell)

Example: (BOND) 10/20/09 STMT & RE cr*9/21/09 stmt* ----(in Col A)

In the example above the newest date entry is always put at the
beginning
of
the cell and that is the date I want to extract in a separate column.
(I
only want to pull out dates that have Month, Day and Year in the format
example 9/21/09)
A date is not always preceded by text.

In the example above what I would want to see is:

Column B: 10/20/09
Column C: (BOND) ----everything before the date
Column D: STMT & RE cr*9/21/09 stmt* ----everything after the date
If there is a worksheet function that will do that it would be great.
If
not
I can do a macro, but I am not that experienced with those, so if you
could
lead me through I would appreciate it.
Donna

Check your previous post

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Donna" wrote in message
...
Hi,
I did have one more question and I wasn't sure since I had rated it if it
would come up again. When I used the formula on these examples

(BOND-NO NEW BONDS) Filed Chapter 11 Bankruptcy 9/15/09
It pulled out in the first column-- 11 and not the expected 9/15/09
and
(PERMANENT EXCEPTION) 3/09 10/05/09 stmt
It pulled out 3/09 instead of the expected 10/05/09

Is there a way to specify only to pull out the date if it is in a date
format 10/27/09?
Thanks Donna

"Ashish Mathur" wrote:

Click to show or hide original message or reply text.


You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Donna" wrote in message
...
Thank you veerrry much!! It does exactly what I need it to do!!
Donna
"Ashish Mathur" wrote:

Hi,

Assume the sentence is in cell A3. To get the date, use this formula
in
B3

=TRIM(MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&" 0123456789")),SEARCH("
",A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"01234567 89")))-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")) ))

To get everything before the date, use this formula in cell C3

=TRIM(LEFT(A3,SEARCH(B3,A3)-1))

To get everything after the date, use

=TRIM(RIGHT(A3,LEN(A3)-LEN(B3)-LEN(C3)-1))

Hope this helps

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Donna" wrote in message
...
How can I pull out the 1st date listed in the beginning of a cell of
text.
(new entrys are put at the beginning of the cell)

Example: (BOND) 10/20/09 STMT & RE cr*9/21/09 stmt* ----(in Col A)

In the example above the newest date entry is always put at the
beginning
of
the cell and that is the date I want to extract in a separate
column.
(I
only want to pull out dates that have Month, Day and Year in the
format
example 9/21/09)
A date is not always preceded by text.

In the example above what I would want to see is:

Column B: 10/20/09
Column C: (BOND) ----everything before the date
Column D: STMT & RE cr*9/21/09 stmt* ----everything after the date
If there is a worksheet function that will do that it would be
great.
If
not
I can do a macro, but I am not that experienced with those, so if
you
could
lead me through I would appreciate it.
Donna