Converting e.g. 28-07-2004 to recognised date format

I used xl2002 when I tested and it worked ok for me.

Ron Rosenfeld wrote:

On Fri, 20 Aug 2004 14:57:42 -0700, Gord Dibben gorddibbATshawDOTca wrote:

When I copied the data including underscore and sent it through T to T it
changed the data to dates and deleted/ignored the underscore character
automagically.

Interesting. It did not do that here (Excel 2002). As a matter of fact, even
with T to C and setting the underscore as a separator, only some of the entries
were changed.

--ron

--

Dave Peterson

Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data format"DateDMYFinish.)
which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the same
source. The cells are formatted General, and have 19 spaces after the
last digit in each. I have tried several solutions to clean the data
and convert it to a number with no success.

Some of the solutions I've tried already:

I have tried CLEAN, then using VALUE on the result to convert to a
number, but this returns #VALUE!

I have also tried find and replace on the spaces.

I have also tried various macros recommended elsewhere.


My thinking is that the spaces are in some way not normal. I attach a
sheet with example data.

Any comments appreciated.

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=655942
---
Message posted from http://www.ExcelForum.com/

clay

This worked for me on your sheet.

Sub NumFormat()
Dim cel As Range
On Error GoTo endit
For Each cel In Selection
cel.Value = cel.Value * 1
cel.numberformat = "0.00"
Next cel
Exit Sub
endit:
MsgBox "No cells found!"
End Sub

Gord Dibben Excel MVP

On Sat, 21 Aug 2004 06:47:57 -0500, claytorm
wrote:

Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data format"DateDMYFinish.)
which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the same
source. The cells are formatted General, and have 19 spaces after the
last digit in each. I have tried several solutions to clean the data
and convert it to a number with no success.

Some of the solutions I've tried already:

I have tried CLEAN, then using VALUE on the result to convert to a
number, but this returns #VALUE!

I have also tried find and replace on the spaces.

I have also tried various macros recommended elsewhere.


My thinking is that the spaces are in some way not normal. I attach a
sheet with example data.

Any comments appreciated.

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=655942
---
Message posted from http://www.ExcelForum.com/