Extraction

[Donna]

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

 

[Jacob Skaria]

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

If this post helps click Yes

 

[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

 

[Ashish Mathur]

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&"0123456789")))-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

 

[Jacob Skaria]

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

 

[Donna]

Thank you veerrry much!! It does exactly what I need it to do!!
Donna

 

[Jacob Skaria]

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

If this post helps click Yes

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[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]

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]

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]

Hi,
I have tried it on all known situations and yes it works. I did change the
2nd part to I3 (there were 2 J3's). I am going to forget about the other
part of listing everything after the date, but if we can still keep the part
where it lists the information before the date. Example:
Permanent Exception (3/09) 10/15/09 stmt
So I would want Permanent Exception (3/09) in a separate column. That was
alot ------of work for you and in the future I will try to explain all the
possible situations so as not to cause you so much trouble. This is amazing
and will truly help me in my job. Many Thanks Donna

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com