Search code examples
google-sheets

Google Sheets: Extract Username from Hyperlinked Email Adress

I am working on a spreadsheet with over 20+ users and most users will type their full name and hyperlink their name to their email address. Is it possible to extract and just grab their username (e.g. kennysmith01@ jenniferle023@)?

Example Google Sheet.

enter image description here

Solution

  • About your following question,

    Is it possible to extract and just grab their username (e.g. kennysmith01@ jenniferle023@)?

    I think that it's yes. In your situation, how about using Google Apps Script? When Google Apps Script is used, a sample script is as follows.

    Sample script:

    Please copy and paste the following script to the script editor of your provided Spreadsheet. And, please confirm the sheet name and the range of a1Notation. And, save the script.

    function myFunction() {
  const sheetName = "Sheet1";
  const a1Notation = "A2:A4";

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const rv = sheet.getRange(a1Notation).getRichTextValues();
  const res = rv.map((r, i) => r.map((c, j) => {
    return {
      row: i + 1,
      column: j + 1,
      names: c.getRuns().reduce((ar, s) => {
        const link = s.getLinkUrl();
        if (link) {
          ar.push(link.replace("mailto:", "").split("@")[0]);
        }
        return ar;
      }, [])
    }
  }));
  console.log(JSON.stringify(res));
}

    Testing:

    When this script is run to your provided Spreadsheet, the following result is obtained. This value can be seen in the log.

    [
  [{"row":1,"column":1,"names":["ke###","je###"]}],
  [{"row":2,"column":1,"names":["ja###"]}],
  [{"row":3,"column":1,"names":["je###","ro###"]}]
]

    If you want to add @ to the retrieved value, please modify the above script as follows.

    From

    ar.push(link.replace("mailto:", "").split("@")[0]);

    To

    ar.push(link.replace("mailto:", "").split("@")[0] + "@");

    Note:

    • This sample script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

    References: