Convert String To 10 Key Phone Keypad Digits

Question

Thank you for your time and trying to help me

I'm trying to make a google sheet formula that will convert a string to the Keypad numbers on a 10 key phone both in single digits and in multiple digits for example formula 1 with the input "AHO" should return "246" and formula 2 with the input "AHM" should return "244666"

If this is not possible with a formula maybe this is possible with a Custom Function with App Script

Thank you so much

Answer 1

I have created a custom function which requires a string input. The string input is then converted to uppercase letters for uniformity and to match the letters to the appropriate phone keyboard number mapping. Please refer to the custom function below:

/**
 * Translates letters into 10 key phone keypad digits
 *
 * @param {inputString} The input string to be converted.
 * @return The numerical string output.
 * @customfunction
 */

function convertToKeypad(inputString) {
  var outString = "";
  const letterMap = { "A": '2',
    "B": '22',
    "C": '222',
    "D": '3',
    "E": '33',
    "F": '333',
    "G": '4',
    "H": '44',
    "I": '444',
    "J": '5',
    "K": '55',
    "L": '555',
    "M": '6',
    "N": '66',
    "O": '666',
    "P": '7',
    "Q": '77',
    "R": '777',
    "S": '7777',
    "T": '8',
    "U": '88',
    "V": '888',
    "W": '9',
    "X": '99',
    "Y": '999',
    "Z": '9999',
    " ": '0'
  };
  for (let i = 0; i <inputString.length; i++) {
    outString += letterMap[inputString.toUpperCase()[i]];
  }
  return outString;
}