Copy only date.

[jjmmdanp]

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.

 

[Dave Peterson]

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?

 

[jjmmdanp]

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?

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

 

[Ron Rosenfeld]

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

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

 

[jjmmdanp]

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 ?

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- Ocultar texto entre aspas -

- 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

 

[Ron Rosenfeld]

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

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

 

[jjmmdanp]

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

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

 

[Ron Rosenfeld]

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

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

 

[jjmmdanp]

João,

I think we have a language problem.  I think I am not understanding whatyou
have done and what you want.

I thought, from what you wrote above, that you had used the UDF I suppliedto
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- Ocultar texto citado -

- 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

 

[Ron Rosenfeld]

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 ?

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

 

[jjmmdanp]

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 insteadof
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)                        (COLE)
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 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

 

[Ron Rosenfeld]

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

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

 

[jjmmdanp]

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

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

Now it´s OK, works fine.

Thanks for your patience.

João.

 

[Ron Rosenfeld]

Thanks Ron

Now it´s OK, works fine.

Thanks for your patience.

João.

Glad to help.

Where are you located, by the way?
--ron

 

[jjmmdanp]

Glad to help.

Where are you located, by the way?
--ron

Sory but i dont understand your question.

Where i am ??

Portugal

João

 

[Ron Rosenfeld]

Sory but i dont understand your question.

Where i am ??

Portugal

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

 

[jjmmdanp]

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- Ocultar texto citado -

- 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

 

[Ron Rosenfeld]

Ok.
I live in the mainland in the district named Bragança, attached at
Spain. You are welcome
You live in USA ?

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

 

[jjmmdanp]

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 areaaround
Lisboa, but we enjoyed our visits.
--ron- Ocultar texto citado -

- 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