Library › Coding AI › Office and admin

Standardize messy phone numbers with one regex formula

Practice: Showing an example of what you want. About 5 minutes.

About this exercise

The vendor list in column A has phone numbers typed every possible way. Write one Google Sheets formula for B2 (filled down) that strips everything but digits, keeps the last 10 digits so a leading 1 or +1 is dropped, and formats them like 503-555-0142 (hyphens, no parentheses), because the phone system import rejects anything else. If fewer than 10 digits remain, show CHECK. One code block, then under 25 words outside the code.

Common mistakes when you ask AI for this

  • Not showing the target format. The AI picks its favorite format, often (503) 555-0142, which the import rejects. Try: Add an example: "Format like 503-555-0142, hyphens only."
  • Forgetting the country code. The AI keeps the leading 1, so some numbers end up 11 digits long. Try: Add: "Keep only the last 10 digits."
  • Not saying what to do with short numbers. The AI returns a broken number or an error for 555-0123. Try: Add: "Fewer than 10 digits: show CHECK."
  • Getting a menu of options. The AI likes to offer two or three versions of the formula, so you're left to pick and test them yourself. Try: Add: "Give one formula only, in one code block, no alternatives."
  • Getting a tutorial. By default the AI explains every part of the code at length, which you then have to scroll past. Try: Add: "After the code, one line under 10 words. Nothing else."

What a strong prompt includes

  • Shows the target format
  • Drops the country code
  • Handles short numbers
  • Asks for one answer only
  • Limits the explanation

Practice this now ↑

Related exercises