I have an excel sheet with two columns. One of the columns is the time column. It has over 10000 rows. The format of the time is: '2018-07-18 05:00:00+0530'. The '+0530' is present in each row. I want to remove that from each row of the sheet. How should I do that?
Asked
Active
Viewed 68 times
0
-
Find that text and replace it with nothing? (Ctrl+F). – BigBen May 26 '20 at 13:48
-
I have tried that. It removes the entire string with ###### – Ayush Sinha May 26 '20 at 13:54
-
Widen the column. You'll see that Excel has now converted your *time-that-looks-like-a-string* to an actual time. You can apply formatting as needed. – BigBen May 26 '20 at 13:55
-
It has changed to something like this: 2018-07-18 05:00 – Ayush Sinha May 26 '20 at 14:31
-
The last :00 is missing – Ayush Sinha May 26 '20 at 14:31
-
^ Change the number format to "reapply" it. – BigBen May 26 '20 at 16:32
1 Answers
0
With data in column C, in D1 enter:
=LEFT(C1,LEN(C1)-5)
and copy downward.
If you attempt to edit the data "in-place", you may end up also re-formatting it.
(this assumes the single quotes are in your question, but not in your data.)
Gary's Student
- 95,722
- 10
- 59
- 99