function extractMobileNumbers() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getRange('A:A'); var values = range.getValues(); var extractedMobileNumbers = []; var maxMobileNumberCount = 0; var mobileRegex = /(\+\d{1,3}\s?)?(\d{3}[-\.\s]?)?\d{3}[-\.\s]?\d{4}/g; for (var i = 0; i < values.length; i++) { var mobileNumbers = []; if (values[i][0] !== "" && typeof values[i][0] === "string") { var mobileMatches = values[i][0].match(mobileRegex); if (mobileMatches) { mobileNumbers = mobileMatches.map(function (mobileNumber) { return [mobileNumber]; }); if (mobileNumbers.length > maxMobileNumberCount) { maxMobileNumberCount = mobileNumbers.length; } } } extractedMobileNumbers.push(mobileNumbers); } // Adjust the range dimensions based on the maximum mobile number count var outputRange = sheet.getRange(1, 10, extractedMobileNumbers.length, maxMobileNumberCount); // Fill any remaining empty cells with empty strings var emptyMobileNumbers = new Array(maxMobileNumberCount).fill([""]); for (var i = 0; i < extractedMobileNumbers.length; i++) { extractedMobileNumbers[i] = extractedMobileNumbers[i].concat(emptyMobileNumbers.slice(extractedMobileNumbers[i].length)); } outputRange.setValues(extractedMobileNumbers); }
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter