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

10 Answers 10

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

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

Use the CONCATENATE function. E.g.:

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

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

share|improve this answer

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

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

share|improve this answer

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

In Google Spreadsheets (or Google Sheets as they seem to be calling it now) there are two good options.

  1. Append a single quote to the beginning. For example with the phone number +1 415 655 1234 you would enter '+4156551234. This will be displayed in the Sheet at +4156551234. It will also export to a CSV as +4156551234 with out the ' which is extremely useful if you're importing into say a Sales CRM like Salesforce.

  2. Format the cell to Plain Text and append a space to the beginning of the phone number. For example with +1 415 655 1234 you would enter " +4156551234". This will export with the white space before the number though.

Hope that helps!

share|improve this answer

Another way to do this using formulas is to add another row next to the one that you want to change into text. After you do this, inside the row, add =TEXT(G17,"+00000"). Then copy the formula down to the rows beneath it. Once you have done this, hide the row with the old numbers (Which should still be as a formula). This should make it so that the +420123456789 turns from 420123456789 in the box and =420123456789 in the answer box into just =420123456789. Hope I could help you!

share|improve this answer

Yet another option:

Add this, from the menu option Format, under Number>More Formats>Custom number format...

"+"#
share|improve this answer

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.