Trying to find best method for inputting phone numbers

Request

For my org, I'm trying to set up a spreadsheet that can accept phone numbers in most forms without running any formulas. Example:

If someone on my team loads a phone number into Sheets that looks like this "+1-555-555-5555", Sheets will attempt to run a formula and completely destroy the phone number. It will do anything from adding and subtracting all the included numbers to changing the number to something like "0.00E+00". I just want the phone number to stay intact and not mess up the data.

I've been trying to work something out for like an hour and even had someone else look at it and still no solution. Any help appreciated.

Posted Jun 26, 2020, 5:37 PM UTC

310
robogo•

What if you format the cell as text?

1
jsbisviewtifulOP•

That doesn't seem to help - If there is a + at the beginning of the number it still tries to calculate everything out.

1
c_for•

Use a single quote mark:

'

at the start of every entry. The ' will not be shown and it will force the cell to be a text cell.

1
jsbisviewtifulOP•

The use case I'm formatting for is so my team can paste numbers into the cell so I'm not sure that would work.

They would be trying to dump large amounts of data into the spreadsheet. Is there an 'If" statement or some kind of formatting I can apply?

1
jaysargotra•

Format to plain text and add a space in the beginning of the number

1
jsbisviewtifulOP•

The use case I'm formatting for is so my team can paste numbers into the cell so I'm not sure that would work.

They would be trying to dump large amounts of data into the spreadsheet. Is there an 'If" statement or some kind of formatting I can apply?

1
6745408•

Ditch the symbols and format the columns as +# (###) ###-####

If you're working with a long list of numbers already, use

=ARRAYFORMULA(
  IF(ISBLANK(A2:A),,
   VALUE(
    REGEXREPLACE(
     TEXT(A2:A,"0"),
     "[^[:digit:]]", ""))))

... then use the formatting on this column.

1
6745408MOD•

Did you get this sorted out? If so, can you update the flair?

1
jsbisviewtifulOP•

Going to try 6745408's method soon but if that does not work then I am still looking for a solution.

1
6745408•

perfecto! If you want a demo, reply to my other comment and I'll whip one up. :)

1