70

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?

14 Answers 14

59

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

4
  • I knew that, that works in Excel and OpenOffice too, but using it all over doesn't look too pretty :-]
    – Ivan
    Oct 15, 2012 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, 2012 at 11:56
  • 5
    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, 2012 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 '?)
    – MrWhite
    Oct 16, 2012 at 12:32
25

Guide to Adding Custom Number Formats

  1. Click on Format
  2. Go to Number > More Formats > Custom number formats
  3. Paste custom number format (see below)
  4. Click Apply

.


.

Different Custom Number Formats

Output: ###-###-####

Syntax: ##########000"-"000"-"0000


Output: (###) ###-####

Syntax: ##########"("000") "000"-"0000


Output: 1-###-###-####

Syntax: ##########"1-"000"-"000"-"0000


Output: (##) ####-####

Syntax: ##########"("00") "0000" "0000


Output: +000000000000

Syntax: #"+"000000000000


1
  • Veryuseful! tks
    – MagicHat
    Feb 21, 2021 at 23:50
6

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!

5

Yet another option:

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

"+"#
3

Use the CONCATENATE function. E.g.:

=CONCATENATE("+", "123456789")
2

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

2

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

1
  • 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, 2013 at 17:59
2

I use this custom number format for telephone numbers: "+"## ### ### ### it results in something like this: +49 123 456 789

1

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.

1

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!

1

In the header you should see, just under "Data" in the menu, a 123 with a down carat. Hovering on this shows "More Formats". Try selecting "Plain Text" there.

1
  • A screenshot with this would be really helpful to future users. Aug 13, 2015 at 15:28
0

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

0

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

1
-1

pour moi en cellule C1 j'ai : '0712345678 et en cellule C2 j'ai =TEXTE(C1;"+33#"" ""0"" ""00"" ""00"" ""00"" ""00") Résultat en C2 j'obtiens +33 7 12 34 56 78

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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