|
Extraction
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 |
Extraction
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 |
Extraction
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 |
Extraction
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 |
Extraction
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 |
Extraction
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 |
Extraction
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 |
Extraction
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 |
Extraction
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 |
Extraction
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 |
Extraction
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 |
Extraction
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" wrote: 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 |
Extraction
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Donna" wrote in message ... 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" wrote: 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 |
| All times are GMT +1. The time now is 07:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com