At the end of last year, I wanted to clean up some mailing addresses in an Excel spreadsheet for use with our family New Year’s cards. I resent having to spend time learning any computer language other than Lisp and SQL unless that language is more powerful than Lisp and/or SQL. Thus, I’ve never become a proficient Excel programmer. Could an LLM come to my rescue?
Prompt to ChatGPT 5.2:
write a Microsoft Excel 365 formula that will take a US address and remove a trailing “US” or “US” preceded by a newline and also turn any 9-digit ZIP code into a 5-digit zip code by removing “-NNNN” where N is a digit.
Answer:
=REGEXREPLACE(
REGEXREPLACE(A1,"(\d{5})-\d{4}","$1"),
"(\r?\n)?US$",""
)Grok’s answer:
=LET(
cleaned, TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)&"US",""),CHAR(10)," ")," US","")),
lastLine, TRIM(RIGHT(cleaned, LEN(cleaned)-FIND("@",SUBSTITUTE(cleaned," ","@",LEN(cleaned)-LEN(SUBSTITUTE(cleaned," ","")))))),
zipLike, TRIM(TEXTAFTER(lastLine," ",-1,"",1)),
fixedZip, IF(REGEXTEST(zipLike,"^\d{5}(-\d{4})?$"),
LEFT(zipLike,5),
zipLike),
SUBSTITUTE(cleaned, zipLike, fixedZip)
)What’s the difference between these, other than Grok’s epic verbosity? The Grok version results in an unexplained error from Excel while the ChatGPT code works.
Advantage: ChatGPT
The main difference is that Grok is using spreadsheet/Excel-specific functions, while ChatGPT is using the more general mechanism of Stephen Kleene’s regular expressions, which are available in Unix and all modern programming languages.
The Grok version is so complex that I can’t justify the time to try to understand it, which is not all that unusual in my experience with LLM-generated code.
The ChatGPT version is impressive, but note that:
a) The code relies on the ZIP+4 having an infix dash
b) The ZIP+4 removal would affect any number, including a street or house number, with that pattern, i.e., “29687-1315 E Retirement Ave”)
c) ZIP+4 was invented for a reason and you probably shouldn’t remove it