Excel - 3 Cool methods to extract text from the beginning of a text string

Posted on: 05/03/2023

We frequently need to extract part of a text string in Microsoft Excel. We are going to extract the first part of a text string. Examples of text string parts you would extract include product numbers, employee IDs, and accounting numbers.

For example, the product number GA-205-Atl or the accounting number 0012346-00123. Any number that is broken up by some delimiter and you need to remove part of the number qualifies. The three methods we're going to use will include the following. Flash fill, the left function combined with the Find function, and finally, a great function in Excel called Textbefore.

YouTube video

Flash Fill

Flash Fill looks for a pattern in Excel data. When it picks up a pattern, it automatically fills in the data. An example of Flash Fill is first and last names are in column A. You can flash fill the first name in column B and the last name in column C.

Flash Fill Video

Left Function

The LEFT function is a text function. It has two arguments. It pulls text starting on the left and the number of characters.

LEFT(text, [num_chars])

If A2 contains ABC-123, the LEFT function =LEFT(A2,3) would return ABC.

Find Function

The Find function has three arguments. FIND(find_text, within_text, [start_num])

Find_text Required. The text you want to find.
Within_text Required. The text containing the text you want to find.
Start_num Optional. Specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

TEXTBEFORE Function

The TEXTBEFORE function will return the text that occurs before a given character or substring (delimiter).

For example, if A2 contains R4T2-AB3-GA44, TEXTBEFORE(A2,"-") returns R4T2

=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])

Lifetime access to Teams Course Beginner to Pro with coupon

Online Microsoft Teams Training with Chris Menard

50% off coupon

Just some of the topics covered

Navigation Teams
Using private Chat
Using group Chat
Teams Meetings
1. Scheduling Meetings
2. Meeting controls
3. Roles in a meeting
4. Sharing resources
5. PowerPoint in a meeting
6. Scheduling Team meetings from Outlook
Working with Teams and Channels
1. Conversations
2. Files
3. Wiki and OneNote
Using Outlook and Teams
Using apps in Teams

Recent Articles

Optimizing Excel Formulas with Copilot: A Comprehensive Guide

Five Methods for Creating Running Totals in Excel

Excel is a powerful tool that offers various features to manipulate and analyze data efficiently. One of the useful functionalities it provides is the ability to create running totals, which can be essential for tracking cumulative sums over a period.

Microsoft Word to Adobe Acrobat with Bookmarks

We don't usually want to email, or share are Word files. Frequently, we make our Word documents PDF files. If you lay out your Word document correctly, it is easy to make bookmarks in the PDF.

Dictate Feature Updates in Word, Outlook, PowerPoint, and OneNote

Dictate in Word, Outlook, OneNote, and PowerPoint allows users to use speech-to-text to author content in files.

Chris Menard

Chris Menard is a certified Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. He runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events every year, presenting at the Administrative Professional Conference (APC), the EA Ignite Conference, the Support Staff Conference, the University of Georgia, and CPA conferences. You can connect with him on LinkedIn at https://chrismenardtraining.com/linkedin or watch his videos on YouTube at https://chrismenardtraining.com/youtube.