How To Reformat Phone Numbers Within One Column? [duplicate]

Question

I have a google sheet in which people's phone numbers are stored. I want them to be displayed in format NNN-NNN-NNNN, and to do this I like to store them in format NNNNNNNNNN set to display that way (although it's not as important how they're actually stored, just the visual end result). Sometimes they are pasted in from somewhere where they are formatted (NNN) NNN-NNNN, or in some other format, and that is inconsistent and causes them to fail to be displayed appropriately. Is there a way I can either:

1. set the formatting such that it will try harder to display various phone number formats as the same NNN-NNN-NNNN format?
2. have the column auto-coerce incorrectly formatted entries people type into it like (NNN) NNN-NNNN, such that it will change the data to NNNNNNNNNN and then display properly?

They challenge here is that I don't want a separate data entry and a data display column, I want to use only one column for this.

Answer 1

Here's a link, though I don't think it would do much. https://docs.google.com/spreadsheets/d/1-tjCZe6JTt-f4gEYISc5-pDvum_pILJIOK5T1vF1utc/edit?usp=sharing

There's two things that needs to happen: format and data cleaning. First format.

I don't think there is a built-in format for phone numbers (but I could be wrong), so we make our own. Select your column, go to your menus > Format > Number > Custom Number Format. You'll see a place where you can input things. enter in 000-000-0000. This will format numbers and numbers only into this format. ###-###-#### is probably the more correct form, but I prefer 000-000-0000 because it adds leading zeroes. But choose which one works better for you.

Now for data cleaning. The best way I could figure is to use replace with regex. press Ctrl + H and check the 'Search using regular expressions'. Set the search to specific range and set it to the column of phone numbers, because this method can be very invasive. in the find field input [()\- ]. This is a regex pattern that will find all parentheses, dashes, and spaces. Keep the replace field blank. Then just to be safe, replace one at a time.

That should do it. But if you are willing to play around with AppScript, you can take this code of mine that I first thought of when answering.

function onOpen() {
  menu = SpreadsheetApp.getUi().createMenu("⚙️ Custom")
    .addItem("Fix all selected phone numbers", "numberFixer")
    .addToUi()
}

function numberFixer() {
  const activeSheet = SpreadsheetApp.getActiveSheet();
  const selection = activeSheet.getSelection().getActiveRange();
  const selectionValue = selection.getValues();
  for (let i = 0; i < selectionValue.length; i++) {
    for (let j = 0; j < selectionValue[i].length; j++) {
      const currentCellValue = selectionValue[i][j];
      if (typeof currentCellValue === 'string' && !currentCellValue.match(/\w/g) && currentCellValue !== '') {
        selection.getCell(i + 1, j + 1)
          .setValue(currentCellValue.replace(/[()\- ]/g, ''))
          .setNumberFormat("000-000-0000");
      }
    }
  }
}

This will create a custom menu that contains a function. When the function is run, it will remove all parentheses, dashes, and spaces if the cell doesn't contain any letters. Afterwards, it will format the cell to a phone number.