Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

5 Answers

up vote 1 down vote accepted

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)
share|improve this answer
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

=VALUE(A2)

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.

share|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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

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.