Reformating from Text (Excel 97)

Topic

New Reply

This post deals with the issue of reformatting cells that are formatted as text to a different format. I can highlight the entire range and change the format, but the formating does not take effect until the cell is “refreshed”. I understand why this occurs and the workaround, but is there an easier way? I have a worksheet with nearly 8000 lines. It was originally imported from a text file, and therefore is formatted entirely as text. My Problem: I have a date field that I’m trying to reformat from text to date. I can accomplish this by changing the format of the entire column to DATE, then hitting F2 for each cell in the column. Is there any way to do this in batch so that I don’t have to several hours reformating several columns cell by cell???

Reply | Quote

Replies

The following works in Excel 2000, but I am not sure what it will do with dates in earlier versions. Make a copy of your workbook before trying it:

1- In any empty cell on the worksheet that is not formatted as text, enter a 1.

2- Select this cell and select Copy from the Edit menu.

3- Select all of the cells with dates formatted as text.

4- Select Paste Special from the Edit menu.

5- In the center section of the dialog box, labeled Operation, click on the radio button next to Multiply.

6- Click on the OK button.

7- With all of the cells containing the dates still selected, select Cells from the Format menu and format the cells with the date format you want.

You should now have dates in the cells.

Reply | Quote

See also this Thread.

Reply | Quote