 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2008Copy only date.
Hello I have a great file with more 1000 entries (lines) like this example ( h**p://img91.imageshack.us/img91/1465/exampleag3.jpg ) , and i need to copy only the "date" of each line (1/7/94) to a new file, or a new column and convert this to a normal date like: 17-7-94 Can someone help me ? Thanks. João. I don't see anything in the URL that looks close to that date. Did you use a wrong example or am I missing something? > Hello > [quoted text clipped - 8 lines] > > João.  SignatureDave Peterson > I don't see anything in the URL that looks close to that date. > [quoted text clipped - 18 lines] > > Dave Peterson Thanks Dave In the cell A2 i only want copy the "1/07/1994" and past it to a new column, exist any away to make this "automatically" ? another software ? Make this manually in 1000 entries is very hard. Another example: http://www.imagehosting.com/show.php/1657909_example2.jpg.html You understand ? Can help me ? Thanks. João >> I don't see anything in the URL that looks close to that date. >> [quoted text clipped - 33 lines] > >João You could use a UDF (requires using VBA). To enter the UDF, <alt-F11> opens the VBEditor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. To use this, enter the formula: =ExtrDate(A1) into some cell where A1 represents the cell where your full entry exists. The UDF assumes the date is part of a text string and in the format of dd/mm/yyyy and it will return a serial number which Excel will interpret as a date. If the result looks like 34516, then format the cell as a Date. ===================================== Option Explicit Function ExtrDate(str As String) As Date Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "\b(0?[1-9]|[12][0-9]|3[01])[\- /.]" _ & "(0?[1-9]|1[012])[\- /.]((19|20)?[0-9]{2})\b" If re.test(str) = True Then Set mc = re.Execute(str) ExtrDate = DateSerial(mc(0).submatches(2), _ mc(0).submatches(1), mc(0).submatches(0)) End If End Function ============================================ --ron > >> I don't see anything in the URL that looks close to that date. > [quoted text clipped - 65 lines] > > - Mostrar texto entre aspas - Thanks Ron It´s work fine, now i have all dates in this format: 21-3-1993, but now i need select only the date and copy this to a new column, you know any solution for this ?? thanks João >Thanks Ron >It´s work fine, now i have all dates in this format: 21-3-1993, but [quoted text clipped - 4 lines] > >João I'm not sure I understand you, but if I do, try this: I am assuming, from the picture you posted, that you are using Excel 2007 I also assume that you mean you want to paste the date itself, formatted as you have, and not the formula. If you need to do this repeatedly, we could change the UDF into a macro that can do that. If it's just one or a few times, you can use this process: 1. Select the range where you now have the dates. 2. On the Home tab of the ribbon, select Copy 3. Select a cell at the top of the new column where you want to paste the dates. 4. Select the word Paste, and, from the drop down menu, Paste Special. From the various options, select "Values and number formats" <OK> --ron > >Thanks Ron > >It´s work fine, now i have all dates in this format: 21-3-1993, but [quoted text clipped - 25 lines] > <OK> > --ron Thanks Ron I try to make this but when i select "values and number formats" in Paste Special and press Ok this copy all i have in the cell, text and numbers. Any sugestion ? Thanks again. João >> >Thanks Ron >> >It´s work fine, now i have all dates in this format: 21-3-1993, but [quoted text clipped - 36 lines] > >João João, I think we have a language problem. I think I am not understanding what you have done and what you want. I thought, from what you wrote above, that you had used the UDF I supplied to extract the date, and were displaying it in your desired format. And that then you wanted to have "just" the date, and not the formula. On your picture, you show: Present What i pretend Member: text, 1/07/1994, 01-07-1994 Member: text, 10/10/1995, 10-10-1995 Member: text, 18/02/1994, 18-02-1994 Member: text, 1/07/1994, 01-07-1994 Member: text, 9/10/1994, 09-10-1994 Member: text, 04/04/1993, 04-04-1993 Using the UDF I provided, and formatting as I described, on the data under Present, results in the output under "What i pretend". Is this not what you want? OR is there something else in addition? --ron > >> >Thanks Ron > >> >It´s work fine, now i have all dates in this format: 21-3-1993, but [quoted text clipped - 67 lines] > > - Mostrar texto citado - Thanks again Ron In image 1 is what i have. In Image 2 is what i get after aply the UDF =ExtrDate(A1) In image 3 is what i want, in the image 2 i only want select and copy the date 24-7-1992 to another column, but "automatically" if not i have to do manualy 1000 cells, and its hard. Did you understand now ? The images: 1- http://www.imagehosting.com/show.php/1666182_1.jpg.html 2- http://www.imagehosting.com/show.php/1666185_2.jpg.html 3- http://www.imagehosting.com/show.php/1666189_3.jpg.html Thanks João >Thanks again Ron > [quoted text clipped - 7 lines] > >Did you understand now ? I think you are not doing what I expect you to have done. I don't understand how you obtain image 2 by using the UDF =ExtrDate on image 1. I see the difference in the date format, with the hyphen separator instead of the slash, but =ExtrDate doesn't do that (or it should not do that). When I use that UDF, entering it in E387, and, of course, using the appropriate cell reference which, on your image, would be =ExtrDate(D387) and not ExtrDate(A1), I get the following: (COLUMN D) (COL E) Member: JULIO1, Dias, 24/7/1992, 33809 Member: MARCO, Marco, 19/02/1994, 34384 Member: BEN, Luis, 25/9/1993, 34237 Member:FABIO, 15/07/1996, 35261 and then, formatting the data in COL E as a date: Member: JULIO1, Dias, 24/7/1992, 24-07-1992 Member: MARCO, Marco, 19/02/1994, 19-02-1994 Member: BEN, Luis, 25/9/1993, 25-09-1993 Member:FABIO, 15/07/1996, 15-07-1996 Did you enter the correct formula in the correct cell? E387: =ExtrDate(D387) (and then fill down as far as required) --ron > >Thanks again Ron > [quoted text clipped - 39 lines] > (and then fill down as far as required) > --ron Thanks Ron Hum... i think i make something wrong in the process. I open the excel file, after i press ALT + F11 to open the UDF, in the left i select my file, after i select insert \ module and i paste the code =ExtrDate(D387) I try with the formula E387: =ExtrDate(D387) What i do after paste this ? Enter ? Save file ? Thanks again Ron >Thanks Ron > [quoted text clipped - 7 lines] > >Thanks again Ron You were supposed to paste the VBA code into the module, and use the =ExtrDate(D387) on the worksheet in Cell E387. Here is the VBA code from my previous posting: ====================== Option Explicit Function ExtrDate(str As String) As Date Dim re As Object, mc As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "\b(0?[1-9]|[12][0-9]|3[01])[\- /.]" _ & "(0?[1-9]|1[012])[\- /.]((19|20)?[0-9]{2})\b" If re.test(str) = True Then Set mc = re.Execute(str) ExtrDate = DateSerial(mc(0).submatches(2), _ mc(0).submatches(1), mc(0).submatches(0)) End If End Function ============================ --ron > >Thanks Ron > [quoted text clipped - 28 lines] > ============================ > --ron Thanks Ron Now it´s OK, works fine. Thanks for your patience. João. >Thanks Ron > [quoted text clipped - 3 lines] > >João. Glad to help. Where are you located, by the way? --ron > >Thanks Ron > [quoted text clipped - 8 lines] > Where are you located, by the way? > --ron Sory but i dont understand your question. Where i am ?? Portugal João >> >Thanks Ron >> [quoted text clipped - 16 lines] > >João I thought that might be the case. My wife is from the Açores (Terçeira), and we visit there a few times a year. I've lots of friends, as well as her family, there. I've only been to the mainland once or twice, though. It is a beautiful country with nice people. --ron > >> >Thanks Ron > [quoted text clipped - 25 lines] > > - Mostrar texto citado - Ok. I live in the mainland in the district named Bragança, attached at Spain. You are welcome :-) You live in USA ? João >> >> >Thanks Ron >> [quoted text clipped - 32 lines] > >João Yes, in eastern Maine -- a very small town named Perry; on the border with Canada. The only area of mainland Portugal we've seen has been the area around Lisboa, but we enjoyed our visits. --ron > >> >> >Thanks Ron > [quoted text clipped - 39 lines] > > - Mostrar texto citado - Ok. Many peoples of Açores and Madeira goes to USA and Canada, in mainland peoples goes to Spain, France, Suisse to search better life conditions. They are many beautiful zones to see like: Alentejo, Algarve, Minho, Trás-os-Montes... João
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.