For some reason, when I import data from a certain unnamed source (toggl.com), some of the time columns have an apostrophe in them.

When I have this data loaded in Calc, is there a quick, easy way to remove these?

The columns look like:


And I want:


I see the =REPLACE function, but I can't seem to get that to work. Maybe I don't understand the steps.


Just use =right(A1,8) and drag it down. Right will always give you the X rightmost characters of the string and given it's consistent in this string, it will be the simplest solution.

| improve this answer | |

If your strings are of unequal length you can use the mid() and len() commands combined.

In your example the command would be: =MID(A1,2,LEN(A1))

Drag it down as in the answer above.

How does it work?

MID returns a sub-string of a string specified as: MID([string], [start index], [end index]). If the [end index] varies, using LEN(A1) specifies the length of the particular string.

| improve this answer | |

The function you are looking for is the text command, SUBSTITUTE("Text"; "SearchText"; "NewText"; Occurrence)

| improve this answer | |

You may try Search For . and Replace With & also in other options check Regular expressions.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.