Forum Discussion
Solved
Changing multiple cell data formats
Dear,
I have a sheet with data as general format and i want to change it to time format. I can do that with going to formats but after i apply it does not change unless i double click on it, I want a way to change it without double clicking on it and then copy that format to all without the need to double click on any.
Please help.
That suggests that the values are seen as text.
Try the following:
- Select the data in a single column.
- On the Data tab of the ribbon, click Text to Columns.
- Without changing any settings, click Finish.
- Format the selection as time, if necessary.
Alternatively (in particular if you have multiple columns with such values):
- Enter 0 in an empty cell.
- Select that cell and copy it (Ctrl+C).
- Select the data.
- On the Home tab of the ribbon, click the lower half of the Paste button.
- Select Paste Special...
- Select Add.
- Click OK.
- Format the selection as time, if necessary
- Clear the cell in which you entered 0.
2 Replies
That suggests that the values are seen as text.
Try the following:
- Select the data in a single column.
- On the Data tab of the ribbon, click Text to Columns.
- Without changing any settings, click Finish.
- Format the selection as time, if necessary.
Alternatively (in particular if you have multiple columns with such values):
- Enter 0 in an empty cell.
- Select that cell and copy it (Ctrl+C).
- Select the data.
- On the Home tab of the ribbon, click the lower half of the Paste button.
- Select Paste Special...
- Select Add.
- Click OK.
- Format the selection as time, if necessary
- Clear the cell in which you entered 0.
- Dear Hans,
The first option worked perfectly.
Thank you.
