Generate Regex and Extraction Formula
When you need to extract specific substrings from messy text using a combination of regex and spreadsheet functions.
Prompt
I have a text column in [SPREADSHEET_APP] with entries like [EXAMPLE_TEXT]. I need to extract [PART_TO_EXTRACT, e.g., email domain, phone area code] using a formula. Provide a regular expression pattern that matches this part and a spreadsheet formula using [FUNCTION, e.g., REGEXEXTRACT in Sheets, custom function in Excel]. Include an explanation of the regex components and how to adapt it for variations.
Fill in these
Swap each placeholder for your own value:
[SPREADSHEET_APP][EXAMPLE_TEXT][PART_TO_EXTRACT][FUNCTION]
Pro tips
- Test the regex on a variety of sample entries to ensure accuracy.
- Ask for a case-insensitive version if needed.
- Request a non-regex alternative if your tool doesn't support regex.
Related prompts
Complex Conditional Formula Generator
Act as a spreadsheet expert. I need a formula for [SPREADSHEET_APP] to calculate [DESIRED_OUTCOME] based on these conditions: [CONDITIONS]. The formula should handle [DATA_RANGE_REFERENCE] and include [SPECIFIC_FUNCTIONS, e.g., IF, VLOOKUP, INDEX-MATCH]. Use best practices for readability and error handling. Output only the formula with a brief explanation of how it works.
View promptClean Messy Text Columns with Formulas
I have a spreadsheet column containing [DATA_TYPE, e.g., full names, addresses, email addresses] that is inconsistently formatted. Common issues include [LIST_ISSUES, e.g., extra spaces, mixed case, inconsistent separators]. Please provide a set of formulas or functions in [SPREADSHEET_APP] to clean this column, transforming the data into a consistent format: [DESIRED_FORMAT]. Include explanations for each formula step. If a single formula is too complex, break it down into helper columns.
View promptConvert Natural Language to SQL Query
You are a SQL expert. I will provide a description of my database tables and a question. Write a SQL query to answer the question. Use clear aliases, comments, and optimize for readability. Tables: [TABLE_SCHEMAS]. Question: [QUESTION]. Include only the SQL code in your response, with a brief explanation after.
View promptExplain Data Trend or Anomaly
I have a dataset about [DATASET_DESCRIPTION]. I observed a [TREND_OR_ANOMALY] between [TIME_PERIOD_OR_SEGMENT]. My hypothesis is [YOUR_HYPOTHESIS, if any]. Help me interpret this finding. Consider factors such as [RELEVANT_FACTORS]. Provide possible explanations, suggest additional data to analyze, and ask me clarifying questions to narrow down causes.
View prompt