How to replicate complex REGEX statements with OpenAI

Intro

It's never easy to manipulate data, extract the appropriate piece of information or do a general clean-up using Regular Expressions (REGEX) on a spreadsheet.

REGEX allow you to define search patterns using a sequence of characters, which can be used to perform complex searches and replacements in text. They can be used to find specific patterns within strings, extract information, or even modify and clean up data.

For example, consider a dataset with phone numbers in various formats: '123-456-7890', '(123) 456-7890', '123.456.7890', etc. You can use a REGEX pattern like \d{3}[-.]\d{3}[-.]\d{4} to standardize these phone numbers into a single format. This pattern matches three digits followed by a dash or dot, another three digits, another dash or dot, and then four digits, allowing you to identify and reformat these phone numbers consistently.

With Rows, you could now use our OpenAI integration to replace those complex expressions with user-friendly, natural language based solutions.

About Rows

Rows is the easiest way to import, transform, and share data in a spreadsheet. It combines a spreadsheet editor, +50 integrations with the tools you use every day, a powerful AI Analyst✨, and a sharing experience to instantly turn any spreadsheet into a web app, a form, or a dashboard.

Connect the OpenAI integration

To connect the integration, open a new spreadsheet, and select the OpenAI integration on the welcome side panel. Alternatively, you can search for the integration inside the Data actions panel.

Inside the Data Actions panel, search for "Apply task" and select the corresponding OpenAI action:

Finally, Connect the integration to get started.

The Free plan includes 20 free uses of the OpenAI integration. Users on the Plus or Pro plans have unlimited access to OpenAI and can use their API key to access any OpenAI model, including fined-tuned models. By default, the OpenAI integrations use the "gpt-4o" model.

Use APPLY_TASK to replicate REGEX

Once the integration is connected, you can start using OpenAI in Rows to clean up data.

Alternatively, you can use the Apply Task function directly in the spreadsheet. Type =APPLY_TASK_OPENAI to see the autocomplete.

All OpenAI functions need to be configured through mandatory and optional parameters, depending on their purpose. Let's go through them.

Task

The apply task function requires the task as the first parameter. This the objective of your REGEX expression, in its natural language version. For example: 'Is it a phone number?' or 'Replace year with 2024'.

You can write the text directly inside the text field in the action wizard, or reference any cell in the table by pointing to it on the editor.

Text

The second parameter is text and represents the text you want to apply your task (i.e. REGEX) on.

The remaining parameters are all optional and commonly used for advanced use cases. Learn more about them in the Sentiment analysis function documentation.

Examples

There are several ways to use OpenAI to replicate REGEX expression output:

• Update portion of a list of strings, e.g. page titles
• Switch the order of elements in a string, e.g. Fist and last names
• Check if a string contains non-ASCII characters
• Check if a number follows the pattern of a credit card number
• Check if a number follows the pattern of a phone-number
• Convert different names to proper noun

Update a portion of a string

Goal

Update the year included into page titles.

Example

1=APPLY_TASK_OPENAI("Replace year with 2024",A2)

Details

• Assumes that A2 contains the page title to update.
• The corresponding REGEX to detect the year in the string would be ^[+]?[(]?[0-9]{3}[)]?[-\s.]?[0-9]{3}[-\s.]?[0-9]{4,6}$

Switch the order of elements in a string

Goal

Switch the First and Last Name in a string.

Example

1=APPLY_TASK_OPENAI("Put the first name first and last name second, separated by a comma",A2)

Details

• Assumes that A2 contains the names to switch.
• The corresponding REGEX to identify the first element of the string would be ^(.+?),

Remove non-ASCII characters from a string

Goal

Detect and remove non-ASCII characters from a string

Example

1=APPLY_TASK_OPENAI("Delete only non-ASCII charaters",A2)

Details

• Assumes that A2 contains the string to clean up.
• The corresponding REGEX to detect non-ASCII characters would be ^\x00-\x7F

Detect phone numbers

Goal

Detect if a number follows a phone number patters.

Example

1=APPLY_TASK_OPENAI("Is it a phone number?",A2)

Details

• Assumes that A2 contains the number to check.
• The corresponding REGEX to detect the phone number pattern would be ^[+]?[(]?[0-9]{3}[)]?[-\s.]?[0-9]{3}[-\s.]?[0-9]{4,6}$

Detect credit card numbers

Goal

Detect if a string follows a credit card number patters.

Example

1=APPLY_TASK_OPENAI("Is it a credit card number?",A2)

Details

• Assumes that A2 contains the number to check.
• The corresponding REGEX to detect credit card pattern would be (^(4|5)\d{3}-?\d{4}-?\d{4}-?\d{4}|(4|5)\d{15})|...)

Convert names to proper noun

Goal

Convert a list of poorly formatted names into First and Last name.

Example

1=APPLY_TASK_OPENAI("Proper noun",A2)

Details

• Assumes that A2 contains the name to convert.
• The corresponding REGEX to detect a person's name (first, last, or both) in any letter case would be ((A-Z)+(\s[A-Z][a-z]+)+)|([A-Z]{2,})|([a-z][A-Z])[a-z][A-Z][a-z]