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

Mastering Excel Slicers: A Comprehensive Guide

Excel slicers are powerful tools that enhance data filtering in Microsoft Excel, making it easier to visualize and analyze your data. In this blog, we will explore how to create and effectively use slicers, as well as their advantages and limitations in comparison to traditional filters.

Mastering Loan Amortization: A Comprehensive Guide to Understanding and Comparing Loans

In today's ever-changing financial landscape, understanding loan amortization is crucial for anyone considering a major purchase, particularly a home. With recent changes in interest rates, it's more important than ever to grasp how loan amortization works and how to compare different loan options.

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 Microsoft Trainer (MCT) and works as a full-time Trainer at BakerHostetler - one of the largest law firms in the US. Chris runs a YouTube channel with 900+ technology videos that cover various tools such as Excel, Word, Zoom, Teams, Gmail, Copilot, Google Calendar, and Outlook. To date, the channel has helped over 20 million viewers. Menard also does 2 to 3 public speaking events yearly, presenting at the Administrative Professional Conference (APC), the EA Ignite 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.