How To Extract Text From a Cell in Excel Using AI

Do you need just a part of the text in an Excel cell?

Maybe you have a list of names and need only the first name. Or perhaps you’re working with dates and want to pull out the month.

Extracting specific text from cells can make your data more manageable and clear. Fortunately, you can now automate the process with AI. Let's show you how to extract text from a cell in Excel using ChatGPT, Claude, and other AI tools.

What Is a Substring in Excel?

In Excel and Google Sheets, a substring is a portion or a segment of text extracted from a larger text string in a cell. For instance, if you have the text "Hello, World!" in a cell, a substring could be just "Hello" or "World" or even a single character like "H" or "o." Extracting substrings can help isolate specific data, such as first names, last names, area codes, or other targeted information within a text.

The Fastest to Extract Text from Multiple Cells in Excel

ChatGPT can generate VBA code to automate text extraction tasks across multiple cells. For instance, if you want to extract the first three characters in each cell from range A1, you can automate it with VBA.

Prompt example:
"Can you give me a VBA code to extract the first three characters in cell range A1 to A10? The results should be in a new column?"

ChatGPT output:

Here’s a VBA code snippet that will extract the first three characters from each cell in the range A1 and place the results in column B (starting from B1):

Sub ExtractFirstThreeCharacters()
Dim cell As Range
For Each cell In Range("A1:A10")
If cell.Value <> "" Then
cell.Offset(0, 1).Value = Left(cell.Value, 3)
End If
Next cell
End Sub

Instructions:

1. Open the Excel file where you want to run this code.

2. Press ALT + F11 to open the VBA editor.

3. Go to Insert > Module to create a new module.

4. Copy and paste the code above into the module.

5. Close the VBA editor and go back to Excel.

6. Press ALT + F8, select ExtractFirstThreeCharacters, and click Run.

Traditional Ways to Extract Text in Excel

Microsoft Excel enables users to extract in various ways. So, you shouldn't have a problem finding an option that suits your needs. Here are some commonly used built-in Excel functions for extracting text:

Method 1: LEFT, RIGHT, and MID functions.

When working with data in Excel, sometimes you only need a specific part of the text within a cell. This is where Excel’s LEFT, RIGHT, and MID functions come in handy. These Excel substring functions allow you to quickly extract parts of a string, whether it’s from the start, end, or middle.

1. Using the LEFT function: This extracts a certain number of characters from the beginning (left side) of a text string. This Excel substring function is useful for tasks like pulling out initials or codes at the start of a cell.

• Formula: =LEFT(text, num_chars)

• How to use it:

  • Begin by typing =LEFT( in the cell where you want the extracted text to appear.

  • Select the cell containing the text you’re extracting from, then add a comma and specify the number of characters to keep.

Example: If cell A1 contains "EXM-7266," entering =LEFT(A1, 3) will display "EXM."

Use the fill handle to drag the formula down the column.

2. Using the RIGHT function: Extracts a specified number of characters from the end (right side) of a text string. This is ideal for pulling suffixes, codes, or names from the end of data.

• Formula: =RIGHT(text, num_chars)

• How to use it:

  • Start by typing =RIGHT( in the cell where the result should display.

  • Select the text cell, add a comma, and enter the number of characters you want to pull from the right.

Example: With "EXM-7266" in A1, =RIGHT(A1, 4) will return "7266."

Drag the formula down the column using the fill handle.

3. Using the MID function: Extracts a portion of text from the middle of a string. With MID, you can specify the starting point and the number of characters you want. Hence, it is useful for isolating specific text within a larger string.

• Formula: =MID(text, start_num, num_chars)

• How to use it:

  • Type =MID( in the cell where you want the extracted text to appear.

  • Select the cell containing the text, add a comma, specify the starting position, then add another comma and specify the number of characters to extract.

Example: If A1 contains "EXM-###-7266," using =MID(A1, 5, 3) will result in "###."

Drag the formula down the column using the fill handle.

Method 2: FIND and SEARCH functions.

The FIND and SEARCH functions in Excel help locate specific text within a string, returning the position of the first character of the searched text. These functions are often paired with LEFT, RIGHT, or MID to extract text dynamically.

Here’s how each works:

1. FIND function:

FIND(find_text, within_text, [start_num])

• find_text: The text you want to find.

• within_text: The text in which you’re searching.

• [start_num]: (Optional) The position within the text to start searching from.

Example: =FIND("a", "Data Analysis") returns 2, since "a" is the second character in "Data."

Notes:

• FIND is case-sensitive, so it distinguishes between uppercase and lowercase.

• It will return an error if the text is not found.

2. SEARCH function:

SEARCH(find_text, within_text, [start_num])

• find_text: The text you’re searching for.

• within_text: The text in which you’re searching.

• [start_num]: (Optional) The position from which to start searching.

Example: =SEARCH("a", "Data Analysis") also returns 2, but unlike FIND, SEARCH is not case-sensitive.

Notes:

• SEARCH is ideal if you’re working with data where text case doesn’t matter.

• Like FIND, SEARCH returns an error if the text is not found.

Practical use with MID

You can combine FIND or SEARCH with the MID function to extract text based on position:

Example: Suppose you have "John Doe" in cell A1 and want the last name.

• Use =MID(A1, FIND(" ", A1) + 1, LEN(A1) - FIND(" ", A1))

• This formula finds the space position, adds 1 (to skip the space), and extracts the remaining characters as the last name.

Method 3: Text to Columns.

The Text to Columns feature in Excel allows you to split text in a cell into separate columns based on specific delimiters or a fixed width. You should use it to break down information like full names, addresses, dates, or codes into more manageable parts. Here’s how to use it:

1. Select the data: Highlight the cells you want to split. For instance, if you have a list of full names and want to split them into first and last names, select the cells with the names.

2. Open Text to Columns: Go to the Data tab on the Excel ribbon and click Text to Columns. This will open the Convert Text to Columns Wizard.

3. Choose Delimited or Fixed Width:

• Delimited: Use this if your text is separated by specific characters, such as commas, spaces, or tabs (e.g., separating names like "John, Doe").

• Fixed Width: Choose this if each part of your text is the same length (e.g., part numbers or codes that are consistently six characters long).

4. Set the delimiters or column width:

• If you chose Delimited, check the box for the delimiter that matches your data, such as Comma or Space. You’ll see a preview showing how Excel will split the text.

• If you chose Fixed Width, click on the ruler in the preview window to set where each split should happen.

5. Select destination: Choose where you want the split data to appear. You can keep it in the same location (it will replace the original data) or select a new location to retain the original data.

6. Finish the process: Click Finish to apply the split. Excel will place the extracted text in separate columns, allowing for easier analysis and organization.

Final Thoughts on "How To Extract Text From a Cell in Excel"

With AI, text extraction in Excel is no longer a challenge. All you have to do is ask for a VBA code or formula that suits your needs. We have also covered the manual methods of extracting texts in Excel for those who prefer a hands-on approach.

For more easy-to-follow Excel guides and the latest Excel Templates, visit Simple Sheets and the related articles section of this blog post.

Subscribe to Simple Sheets on YouTube for the most straightforward Excel video tutorials!

FAQ on "How To Extract Text From a Cell in Excel"

Can I use ChatGPT to extract text in Google Sheets?
Yes! ChatGPT can generate Apps Script code compatible with Google Sheets for similar text extraction tasks.

Do I need to know VBA to use ChatGPT’s VBA suggestions?
No, ChatGPT provides complete code with explanations. So, even beginners can implement it without prior VBA knowledge.

Can ChatGPT handle complex text extraction scenarios that combine multiple conditions?
Yes! ChatGPT can generate formulas and code that combine multiple conditions, such as extracting text based on character position, specific words, or patterns, all in one prompt.

Related Articles

How to Use AI to Find External Links in Excel

How to Make a Box and Whisker Plot in Excel

How to Use AI to Find a Circular Reference in Excel