How to Use AI to Structure an Unstructured Column in Excel

Image by Author

I used only 3 prompts to convert an unstructured data column in Excel to a structured column.

First prompt

Give me a function that will extract only email addresses from a cell and make sure it removes all the other text and special characters

Output: 

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1, FIND(“ “, A1&” “, FIND(“@”, A1))-1), “ “, REPT(“ “, LEN(A1))), LEN(A1)))

This function resulted in <emily.jones@example.com>,

Second prompt

The function you provided is working but it also extracted special characters <, >, and , that are associated with the email value but I do not want these special characters so now create a formula that remove only these characters from <emily.jones@example.com>,

Output: 

=FILTERXML(“<t><b>” & SUBSTITUTE(B1, “<”, “”) & “</b></t>”, “//b”)

This function gave me emily.jones@example.com>,

Third prompt

The function you provided remove only < from <emily.jones@example.com>, but not > and ,(comma). So create a formula that removes > and , as well

Output: 

=FILTERXML(“<t><b>” & SUBSTITUTE(SUBSTITUTE(C1, “>”, “”), “,”, “”) & “</b></t>”, “//b”)

And finally, this resulted in emily.jones@example.com

How I used these outputs

1. There were 471 records so I inserted the first output in B1 and then dragged it up to the end.
2. Entered the second output in C1 then dragged it.
3. Entered the third output in D1 then dragged it.

I do use Excel but never created such complex functions and I have heard even for experts it is a time-consuming process.

So, even though I am not an Expert, I can create complex Excel functions.

What do you think about this use case?

Popular posts from this blog

Top 163 Mega Prompts for Marketing

Image
Image by Author How to use these Mega Prompts for Marketing Either replace [Company Description=] and [Customer Description=] with your company and customer description or add them after the equal(=) sign then paste them in ChatGPT or Gemini or any other ChatBot. 163 Mega Prompts for Marketing  You're an experienced business consultant with a deep understanding of customer behavior, market trends, and strategic planning working with [Company Description=], a company serving [Customer Description=]. Your mission is to dig deep into [Customer Description=] experiences and uncover their core challenges or pain points. By understanding these, you'll guide [Company Description=] in creating solutions that enhance customer satisfaction and boost growth. These challenges could range from inefficiencies in their current solutions to unmet needs or emerging trends that are impacting their industry. Look for patterns in feedback, analyze market data, and consider industry best practices...
Read more

What is Google Colaboratory? An Easy Explanation

Image
Introduction to Google Colaboratory Google Colaboratory is a product that allows anyone to use Python through the browser. Google Colaboratory is a long name so Google gave it a short name Colab to make it easy for users. Few things about Google Colaboratory: Google Colaboratory is developed by the Google Research team and it is particularly well suited to machine learning, data analysis, and education.  Google Colaboratory let everyone create and execute any Python program through the browser.  Technically speaking, Google Colaboratory is a hosted Jupyter notebook service that offers free access to computer resources, including GPUs, and requires no setup to use. That means you do not have to pay to use it. Limitations of Google Colaboratory Resources provided by Google Colaboratory are not guaranteed but they are unlimited with some variations, which means that its usage limit varies. Google says that this non-guarantee and variation in the usage limit is imposed because it ...
Read more

Python vs. R for Data Science 2023: Demand Breakdown.

Image
Python vs. R for data science: In data science, Python and R are the two most widely used programming languages and thousands of people around the world question which one they should use. Photo by Pixabay In this article, I am breaking down the demand for these two languages based on LinkedIn search results that were carried out in two to three days. This search was done for Python and R jobs worldwide and in the top 5 countries. It was impossible to differentiate the demand based on data science only, so I considered all jobs (Mostly developer jobs, specifically data science jobs, or data science-related jobs). Since Python and R are widely used in data science, we will get similar results even if we consider all jobs. This breakdown covers the Python vs R demand based on the following aspects: 1. Python vs R Worldwide demand 2. Python vs R Demand in Top 5 countries 3. Python vs R Demand based on Exp Level – Internship, Entry Level, Associate Level, Mid-Senior Level, D...
Read more

ChatGPT SEO Prompts

Introduction to ChatGPT SEO Prompts These ChatGPT SEO Prompts are highly useful prompts for every person who does search engine optimization (SEO) for his/her own website or on the job. Before moving to these prompts let's have a look at some very simple guidelines about h ow to use these ChatGPT SEO Prompts: Replace "URL" with your website link Replace "article link" with the link to the article which is published on your website Replace "keyword" with your target keyword Replace "article title" with the title of the article for which you want to do SEO Replace "number" with the appropriate integer Replace "article" with the content of the article you have whether published or not Replace "niche" with your niche 41 ChatGPT SEO Prompts 1. Create SEO strategy for "URL" 2. Suggest a list of keywords for "URL" website to target for SEO 3. Suggest blog title ideas for "URL" 4. How to opt...
Read more