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

Posted on:  05/03/2023
Excel - 3 Cool methods to extract text from the beginning of a text string

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.

Excel Left and Find Function

Excel Left and Find Function

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.

Popular Articles

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 in Excel

Flash Fill in Excel

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

  1. Navigation Teams
  2. Using private Chat
  3. Using group Chat
  4. 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
  5. Working with Teams and Channels
    1. Conversations
    2. Files
    3. Wiki and OneNote
  6. Using Outlook and Teams
  7. Using apps in Teams

Recent Articles

April 6, 2024 - Excel Training for MBA Students from the University of Georgia

April 6, 2024 - Excel Training for MBA Students from the University of Georgia

Excel - Advanced Filters

Excel - Advanced Filters

Microsoft Word to Adobe Acrobat with Bookmarks

Microsoft Word to Adobe Acrobat with Bookmarks

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

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

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 18 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 chrismenardtraining.com/linked or watch his videos on YouTube at chrismenardtraining.com/youtube.

Categories