Hi want to format all of the values in a column to be a phone number, but the first character in many cells is


This prevents the cells from being recognized as a number. I can't do a search and replace for that character either even if I escape it with a backslah (\'). How can I format the cells and/or remove that character that seems to be forcing it to be recognized as a string?


EDIT: I just tested my suggestion below the line, and it does not work, but there's good news. Copying those cells with a leading single-quote does not cause the leading single-quote to be copied to the clipboard. However, that means all you have to do is:

  • Select All | Copy
  • Paste into a new document in a text editor
  • Select All | Copy
  • Paste back into a new sheet or document in OO-Calc.

Assuming that:

  • your data does NOT contain formulas that you need to preserve, and
  • you want to remove that leading single-quote from ALL cells,

my first attempt would involve something like this:

  • Insert a blank column in Column A (this is for the "Find" below, so we don't miss any leading single-quote values in the first column of data)
  • Select All | Copy
  • Open up Notepad++ (or your text editor of choice with extended search-and-replace capability)
  • Paste into a new document in Notepad++
  • Ctrl+H to Search-and-Replace
  • Check the option for Extended search/replace
    • Find: "\t'" (backslash followed by tee followed by single-quote, this means search for Tab followed by single-quote)
    • Replace: "\t" (Tab)
  • Replace All
  • Select All in the document | Copy to clipboard
  • Paste into a new sheet or OO-calc document (when prompted, select the option to use Tab as the separator)
| improve this answer | |

Assuming all your phone numbers are ten digits long, use the formula =RIGHT(A1,10) to strip off the leading asterisk.

| improve this answer | |

I was able to reproduce your problem. The workaround I came up with is a little less elegant than I'd like it to be, but the following steps might work for you.

  1. Insert a new column next to the one you are having a problem with.
  2. Copy the column with the values that start with '
  3. Select the new column you created in step 1.
  4. Choose Paste Special and select Values.

The values will paste in the new column without the ' and you can then delete the original column.

I couldn't get it to work by pasting Values into the original column which is why I included the step of pasting them into another column.

There are a few scenarios where this might not work. If you have other formatting that you need, or if you have formulas in that column as well, this would break those things.

| improve this answer | |

Create a new column and use the VALUE function, like so:


This assumes that your data is in column A.

EDIT: What you should do is create the new column (let's say it's column B), then copy the above formula into the first cell. Copy the formula down the entire column. Then, create a new column, and copy column B, then paste the text only into column C (use Paste Special). Then you can get rid of columns A and B altogether.

| improve this answer | |
  • When I do this, the value for cells that had the ' character are: Err:502 – MikeN Nov 13 '09 at 18:06
  • I've updated my answer with some more details. – Tim S. Van Haren Nov 13 '09 at 18:12

You can amend the list, in place, using the "Text to columns" function.

Simply select the affected cells, go to Data > text to columns (either via menus or on ribbon if 2007 or later), accept the default choice "delimited", go Next, clear all selections (so Tab is cleared as a delimiter), next, accept choice of General format for cells, Finish. The leading apostrophes are simply dropped along the way. You can apply this to multiple columns as well.

Job done.

Also useful if there are other character(s) at begining or end of text, you can specifiy these as the delimiter characters (one can by specified in "other" as well), although this will meant that they move across by one column (or more if there are multiple sequential occurences but a tick in the "treat as one" box fixes that). So I thought it would be a useful answer as it covers a more general method, which also works for things like splitting names into first name surname by delimiting at spaces and so on.

| 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.