Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I am to store international standard phone numbers (starting with a plus sign followed, usually, by plain numbers) in a Google spreadsheet column. An example of such a phone is

+420123456789

When I enter this phone it turns to just 420123456789 to be seen in the cell and =420123456789 to be seen in the formula/value bar. I would like to disable this conversion.

I've tried using FormatNumbersPlain text applying it to the whole column as well as to the particular cell but it doesn't seem to work.

Any ideas how to overcome this?

share|improve this question
add comment

8 Answers 8

If you prefix the data in the cell with a single quote—'—it should prevent any default formatting.

share|improve this answer
    
I knew that, that works in Excel and OpenOffice too, but using it all over doesn't look too pretty :-] –  Ivan Oct 15 '12 at 11:54
    
Moreover, if I do that in GoogleDocs, it adds a second ' and a ' is actually displayed in the document. –  Ivan Oct 15 '12 at 11:56
2  
The single quote only shows if you formatted the cell as plain text. If you leave it in the default number format, the single quote prefix will disappear. I just tested and confirmed in one of my own Google spreadsheets. –  efgen Oct 15 '12 at 14:52
    
As @efgen says. Just to add... the single quote still appears in the formula bar, but it is not considered part of the data. (Curious, at no time for me does it add a second '?) –  w3d Oct 16 '12 at 12:32
add comment

Make sure to add a space between ' and + signs. The ' will not show, only +(numbers).

share|improve this answer
    
I have tried lots of setting for this issue but failed. Jacob's solution is interesting. I use to copy/paste mobile numbers from other location to spreadsheet. These numbers don't have international code. Is there way to automatically create prefix of International code e.g. +91 or +42 before each number I paste in spreadsheet cell? If we can create currency symbol then there must be way to create custom prefix... developers should take in account. –  user42394 Jun 20 '13 at 17:59
add comment

Use the CONCATENATE function. E.g.:

=CONCATENATE("+", "123456789")
share|improve this answer
add comment

Using ' does not work if formatting is set to NumbersPlain Text. Use "Clear Formatting" instead.

share|improve this answer
add comment

I had the same trouble, and solve it using the following formula format:

="+69"

to achieve having the cell looking like

+69

Inconvenient, but best I could manage.

share|improve this answer
add comment

You just need to wrap your text in formula like this: =("+1+2+3-123").

share|improve this answer
add comment

I was able to solve this by doing "clear formatting" on the column, and then adding a space before the plus symbol.

share|improve this answer
add comment

NONE OF THIS WORKS!!!!

If you are a dev that works on this crap.....STOP CHANGING DATA WHEN YOU ARE NOT ASKED TO!!! very very very simple you would think

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.