Google-sheets – How to auto format a phone number with an associated hyperlink in Google Sheets

google sheets

I don't know if this is possible, as its a bit of an edge case, but it would be amazing if this can be accomplished.

I have a spreadsheet of contact information, with fax numbers. At work, we send faxes via email by sending it to [faxnumber]@FaxAdmin.

What I would like to accomplish is two-part.

If I type in a 10 digit number (ex:1112223333) in A1, the following would occur:

  1. It would auto-format the contents into: 111-222-3333.
    This is fairly easy to accomplish already, I believe. The next part is where Im stuck
  2. It would then format 111-222-3333 as a hyperlink in the following format:
    "mailto:1112223333@FaxAdmin","111-222-3333"

Is this possible?

Best Answer

Short answer

Yes, it's possible and there are several ways to achieve this. This answer will focus on using built-in features.

Explanation

This answer assumes that is possible to have one column for data entry and another column to hold the hyperlinks. The data entry column could be shown/hidden according to fit sheet the needs of the different uses.

Regarding #1,

Click on Format > Number > More Formats > Custom Number Format, write the following in the box

###-###-####

then click on the Apply button.

Regarding #2,

Simple formula

Add the following formula to B1

=HYPERLINK("mailto:"&A1&"@FaxAdmin",TEXT(A1,"###-###-####"))

then fill down as necessary.

Advanced formula

To automatically fill a column with hyperlinks created with data from column A, use the following formula:

=FILTER(
    ArrayFormula(
        HYPERLINK("mailto:"&A:A&"@FaxAdmin",TEXT(A:A,"###-###-####"))),
    LEN(A:A)
 )
Related Topic