Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

For some reason, when I import data from a certain unnamed source (, 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.

share|improve this question
up vote 1 down vote accepted

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.

share|improve this answer
That is just sweet - thanks. – mtyson Feb 7 '12 at 19:32

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.

share|improve this answer

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

share|improve this answer

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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