Remove ' from in front of a value in xls or OpenOffice

Do a search/replace

It doesn't pick up the '

Works fine in Excel I can select ' and then leave a blank for the replace and it works fine.

Works in my version of Open Office as well.

Not in my Open Office ver 3.3.0
sample text:
'00:01:06
New-Bitmap-Image.pdf

Try copying the character from the cell and then paste it in the search box.

Or upload a small portion of your file which represents the problem.

See attached
Untitled-1.xls

I cannot see any cell in the attached file which has the leading '

Have you attached the correct file?

Yeah same here what cell are the ' supposed to be in??

Its correct, open it with Open Office. My xls removed the '
See the duration field

In the file you have uploaded (XLS) there is no field with the name of duration.

Sorry, column E

Okay attached is what I see when I open the file.
 

See what I said at 10:51 pls

In a separate cell, eg J1, type formula:

=VALUE(E1)

Copy down the required number of rows.

Copy the new formula cells and paste special values over the incorrect cells. Then correct the time format if so required.

Thanks
Rob H

Tx. However I need to get the corrected data back into the Column E. If I choose copy and paste special - nothing pastes...This is in OpenO

Looks like you need the same for column B.

In J1 put the following formula:

=VALUE(B1)

Copy across columns K L & M and down the required rows.

Select the cells in J K L & M and copy. Do a paste special values in B1. This will overwrite the times in columns B & E with true times and numbers in C & D with true numbers. The numbers in C & D already look OK but in case they aren't this will correct. This assumes that all data in C & D is numeric as per sample.

If not, just do value formulas in J & M and then either paste separately or select columns J to M as before but when doing the paste special, tick the skip blanks option.

Thanks
Rob H

@shaunwingin I have and here's the same in my version of Open Office.

Still no '

 

Simplest solution. Tx

Rob, Can you pls explain when one would use:
"paste special, tick the skip blanks option"
Tx

I often use Skip Blanks when transferring data between workbooks.

I have a excel workbook which I use to work out a weekly Sales & Cost report. This file contains the data for the current month and data is updated each week.

I have also have a workbook which keeps a historic position showing the weekly figures.

I have a sheet setup within the first workbook that pulls data from that workbook and puts it into the format for the second workbook with sums between the lines of data for checking purposes.

I then have a column which pulls just the data and not the sums ready to copy into a separate file.

I can highlight the whole column (or at least all rows) and "paste special values skip blanks" into the other workbook. The blank cells don't then overwrite/delete the formulas which are then in the other workbook for the summary lines.

If I didn't do the skip blanks, I would have to select separate blocks of data each time, or I would have to link the files but would have to ensure I overwrote values each week so that the link pulled the update and kept the previous week.

Thanks
Rob H

Tx 4 the great lesson! Much appreciated.