Convert Excel cells stored as text to date and number coming from notepad

[Fire_d]

Hi all !!


I am using the code below from the great Leith Ross to import delimited text from an unsaved file opened on Notepad to an Excel worksheet.

The problem is that coping all and pasting all the text from notepad to a worksheet is giving errors on the numbers and dates. Appears little green triangles on the left top o the cells asking to covert text to numbers and 2 year digit date to a 4 year digit date.

I tried to uncheck the Text date with 2 digit years and Number stored as text in the Excel error checking options as Leith suggested (I could use code for that). That will just make the green triangles disappear.

If I try to custom the format of the date like 25-Jul-08 or make operations with the numbers using vba code it won't work.

Strange enougth is that if copy all the text from notepad and paste on the worksheet it give no errors in the numbers and will make all 2 digit year date into 4 digit... just curious why no code can change the cells perhaps when I paste I need do some sort paste special....

The text file is attached and the code that is pulling the text done Leith is below :

Please Login or Register  to view this content.

Link from previous threat:

http://www.excelforum.com/excel-prog...epad-text.html

[arthurbr]

Don't know how far this helps but this little trick might
Enter 1 in an empty cell and copy the value
Select all your values withe the triangle in xl - Right Click - Select Paste Special - Check " multiply" and Ok

[Leith Ross]

Hello Fire_d,

I solved the issue with the dates by applying formats to columns with dates. The same could be done for the numbers and currency. I am still mystified by why setting the column formats in Excel doesn't behave the same as when done with VBA. Here is the update macro and sample workbook you run also.

Please Login or Register  to view this content.

Main Macro

Please Login or Register  to view this content.

[Fire_d]

Hi arthurbr,

OK that will work, but I need to solve the problem with VBA code.
Coverting what you suggested into code won't work unfortunately.

Thanks for your reply !

[Fire_d]

Hi Leith,

The cells get formatted and I just can see that by right clicking the cell to see the formats.
The cells simple does not respond to the formatting.
In the Format Cells screen it shows that the cell is formatted but it does not update on the cells.
I still getting green triangles and cannot make operations with numbers nor see the date formatted properly.

The cells gets formats only manually like "F2" and "Enter". You enter the cells as for editing and go out.

The code I did below solve the problem leaving the macro slow. I do not like of using this methods.
See if you can help with another idea. Anyway I will test your code at my work and tell if I get better results.

Please Login or Register  to view this content.

Thank you and Regards,
Fire_d