Use ChatGPT to Write Formulas in Google Sheets

Handling data in spreadsheets often involves one-off data cleanup, for example, splitting names or extracting zip codes. ChatGPT, a handy conversational AI, makes it easier to write formulas for these tasks even if you’re not a pro at Google Sheets.

For instance, let’s say you need to split names into first and last, but the regular =SPLIT() doesn’t cut it because you need to split the full name into at most two parts and middle name should be in the second. ChatGPT can help:

ChatGPT writing Google Sheets formula to split first and last name
ChatGPT writing Google Sheets formula to split first and last name

Or, if you need to grab the ZIP code from a string like “60181, Villa Park, Illinois”, ChatGPT’s got you covered:

ChatGPT writing Google Sheets formula to extract ZIP code from a string
ChatGPT writing Google Sheets formula to extract ZIP code from a string

These are just basic examples, but ChatGPT can save time and effort on these and more complex tasks. It can also help you discover solutions you didn’t know about.

However, ChatGPT comes with some limitations:

  1. ChatGPT may come up with different solutions of varying complexity.
  2. Some solutions might not work, so always double-check and test them.
  3. ChatGPT’s knowledge is based on data up until 2021, so it won’t know about functions added after that. For example, it doesn’t know about MAP, BYROW, LAMBDA, etc. introduced to Google Sheet in 2022. December 2023 update: OpenAI have updated ChatGPT and now it knows about recent updates to Google Sheets, including these new functions.

Practical tips:

  1. Be clear and specific from the start about what you need. For example, whether it is a question for Google Sheets or Excel and if you are interested in a formula or script.
  2. If you can, provide examples of the data you’re working with.
  3. ChatGPT 3.5 is often good enough, but ChatGPT 4 is more powerful.
  4. Use the same chat session to tweak your request or the formula.
  5. If you’re not sure about a solution, ask again in a new chat for potentially better answers.