Searching for a specific character in a cell and replacing it with another

Hello there

When I’m doing my budget sheets I copy from the bank’s website into my Calc sheet. The values are pasted into my Calc sheet as a text value.

When I format the cell from a text value to either a date or a currency value then a ’ character is added to the front of the value i.e.:

When pasting from the website a text value is stored in the cell: Jun 29, 2016

When I format the cell to a date value, the value changes to: 'Jun 29, 2016

I can’t get my date and currency formatting to take place until I remove the ’ character. I’m hoping that I can use the find and replace function to search for the ’ character and replace it with a space which will allow the correct formatting to take effect.

I can’t figure out though how to accomplish this. Looking forward to your input.

Regards
Dirk

Use past special to past as unformatted text so you can select in field type for every field clicking on their head.
Menu/Insert/Past special [Crtl+Shift+V]