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

You have a macro solution to your previous post dated 10/20/2009

If this post helps click Yes
---------------
Jacob Skaria


"Donna" wrote:

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

I did try that and it worked. I have never used a sub macro. I have other
things to do in this workseet that I will be setting up in a macro. I then
will share this macro with my team mates and they will copy it. Can everthing
be incorporated into one macro, or do you have set up a macro and then a sub
macro?
Thanks Donna
"Jacob Skaria" wrote:

You have a macro solution to your previous post dated 10/20/2009

If this post helps click Yes
---------------
Jacob Skaria


"Donna" wrote:

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

OK...The formula way

cell B1
=--LEFT(TRIM(SUBSTITUTE(A1,C1,)),FIND(" ",TRIM(SUBSTITUTE(A1,C1,)))-1)

cell C1
=TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789"))-1))

cell D1
=TRIM(MID(A1,LEN(C1)+FIND(" ",TRIM(SUBSTITUTE(A1,C1,)))+1,255))

If this post helps click Yes
---------------
Jacob Skaria


"Donna" wrote:

I did try that and it worked. I have never used a sub macro. I have other
things to do in this workseet that I will be setting up in a macro. I then
will share this macro with my team mates and they will copy it. Can everthing
be incorporated into one macro, or do you have set up a macro and then a sub
macro?
Thanks Donna
"Jacob Skaria" wrote:

You have a macro solution to your previous post dated 10/20/2009

If this post helps click Yes
---------------
Jacob Skaria


"Donna" wrote:

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

Forgot to mention to format ColB to excel date format....as that return a
date...(not a text)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

OK...The formula way

cell B1
=--LEFT(TRIM(SUBSTITUTE(A1,C1,)),FIND(" ",TRIM(SUBSTITUTE(A1,C1,)))-1)

cell C1
=TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789"))-1))

cell D1
=TRIM(MID(A1,LEN(C1)+FIND(" ",TRIM(SUBSTITUTE(A1,C1,)))+1,255))

If this post helps click Yes
---------------
Jacob Skaria


"Donna" wrote:

I did try that and it worked. I have never used a sub macro. I have other
things to do in this workseet that I will be setting up in a macro. I then
will share this macro with my team mates and they will copy it. Can everthing
be incorporated into one macro, or do you have set up a macro and then a sub
macro?
Thanks Donna
"Jacob Skaria" wrote:

You have a macro solution to your previous post dated 10/20/2009

If this post helps click Yes
---------------
Jacob Skaria


"Donna" wrote:

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

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

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

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

Hi,
I did have one more question. 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:

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

Hi,

OK try this

Suppose your string is in cell A3.

In H3, enter
=MIN(SEARCH({"0/?/*","1/?/*","2/?/*","3/?/*","4/?/*","5/?/*","6/?/*","7/?/*","8/?/*","9/?/*"},A3&"0/?/*1/?/*/2/?/*/3/?/*/4/?/*/5/?/*/6/?/*/7/?/*/8/?/*/9/?/*"))
In J3, enter

=MIN(SEARCH({"0/??/*","1/??/*","2/??/*","3/??/*","4/??/*","5/??/*","6/??/*","7/??/*","8/??/*","9/??/*"},A3&"0/??/*1/??/*/2/??/*/3/??/*/4/??/*/5/??/*/6/??/*/7/??/*/8/??/*/9/??/*"))

In J3, enter =MIN(H3,I3)-1
In K3 enter =IF(ISERROR(SEARCH(" ",A3,J3+1)),LEN(A3),SEARCH(" ",A3,J3+1)-1)
In L3, enter =TRIM(MID(A3,J3,K3-J3+1))

Your desired answer should appear inc ell L3. Please try this for all
possible situations and then post back.
Once you are satisfied with this answer, then we will crunch all the
formulas in one cell

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Donna" wrote in message
...
Hi,
I did have one more question. 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:

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

Hi,

Try this to extract the date. This formula assumes that only the date will
carry a /. If there is a slash before the date, then it will extract that
string.

=TRIM(MID(A3,MIN(SEARCH({"0/","1/","2/","3/","4/","5/","6/","7/","8/","9/"},A3&"0/1/2/3/4/5/6/7/8/9/"))-1,IF(ISERROR(SEARCH("
",A3,MIN(SEARCH({"0/","1/","2/","3/","4/","5/","6/","7/","8/","9/"},A3&"0/1/2/3/4/5/6/7/8/9/"))+1)),LEN(A3),SEARCH("
",A3,MIN(SEARCH({"0/","1/","2/","3/","4/","5/","6/","7/","8/","9/"},A3&"0/1/2/3/4/5/6/7/8/9/"))+1)-1)-(MIN(SEARCH({"0/","1/","2/","3/","4/","5/","6/","7/","8/","9/"},A3&"0/1/2/3/4/5/6/7/8/9/")))+2))

hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Ashish Mathur" wrote in message
...
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