Remove the apostrophe in Excel
Solved
dugdi
Posted messages
4
Status
Membre
-
dblabla -
dblabla -
Hello everyone,
I'm facing a rather annoying problem, let me explain:
I have an Excel spreadsheet with about 3000 rows and 15 columns. In one of these columns, I absolutely need to change the duration format from hh:mm:ss to [m]:ss.
The problem is that when I originally copied the data, an apostrophe appears as follows:
'00:03:50
So I cannot change the cell format unless I manually remove this apostrophe, which is considerable over 3000 rows.
If I select the entire column and change the format, it will only change the cells that don't have an apostrophe at the beginning (which means none…). If I understand correctly, the apostrophe serves to lock a cell in text format.
Since the find/replace function does not identify the apostrophe as a cell entry, I cannot resolve my problem with this basic operation.
Thank you very much for your help, I'm struggling.. :)
PS: I'm working with Excel 2003
I'm facing a rather annoying problem, let me explain:
I have an Excel spreadsheet with about 3000 rows and 15 columns. In one of these columns, I absolutely need to change the duration format from hh:mm:ss to [m]:ss.
The problem is that when I originally copied the data, an apostrophe appears as follows:
'00:03:50
So I cannot change the cell format unless I manually remove this apostrophe, which is considerable over 3000 rows.
If I select the entire column and change the format, it will only change the cells that don't have an apostrophe at the beginning (which means none…). If I understand correctly, the apostrophe serves to lock a cell in text format.
Since the find/replace function does not identify the apostrophe as a cell entry, I cannot resolve my problem with this basic operation.
Thank you very much for your help, I'm struggling.. :)
PS: I'm working with Excel 2003
Configuration: Windows XP Microsoft Excel 2003
3 réponses
Hello
you type 1 in an empty cell
then copy
you select your column with the apostrophes
edit - paste special - multiplication
you may need to return it to the correct time-date format
you type 1 in an empty cell
then copy
you select your column with the apostrophes
edit - paste special - multiplication
you may need to return it to the correct time-date format
Sophie
two years later.... thank you Michel_m !!!!!
abcormoran
BJR, AS I WAS ALSO LOOKING WITHOUT SUCCESS, DESPITE YOUR SOLUTION, I MADE THE FUNCTION CNUM THAT CONVERTS TEXT TO NUMBER AND IT WORKED FOR I, FO THANK YOU AB.
DAmien
Awesome!
dblabla
Super simple! Thank you very much! I had been struggling with this problem for a long time... I wonder why I didn’t search for it earlier, am I silly... :-)