Search code examples
google-sheetsarray-formulas

ArrayFormula expansion error while reformatting phone numbers in Sheets

enter image description here

I have a group of multi format phone numbers in a sheets column. I want to remove all non numeric digits and conditionally add a 1 in front (if its not there already). So far I have:

=ArrayFormula( (REGEXREPLACE( E4:E , "([^0-9]+)" , "" ) )

which I have placed in F4. An error has occurred in F4 which you can see in the sheet. When expanded it reads :

Error Array result not expanded because it would overwrite data in F16

F16 is the 803 number on the right. But I WANT it to overwrite all those numbers and reformat the number, What am I doing wrong?

Solution

  • you can't re-format them on the spot by re-writing actual data by ARRAYFORMULA. your options are:

    • you will need to delete the whole F5:F column
    • you will need to put your ARRAYFORMULA into an empty column
    • you will need to restrict your ARRAYFORMULA with ARRAY_CONSTRAIN like:
    =ARRAY_CONSTRAIN(REGEXREPLACE(E16:E, "([^0-9]+)", ""), 12)