Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Filter Data Based on Date in Excel
  • How To Load Analysis ToolPak in Excel
  • Understanding Pivot Tables in Excel
  • Data Series in Excel
  • How to add Trendline to a chart in Excel

References

  • How to use Excel FORMULATEXT function
  • How to reference named range different sheet in Excel
  • How to use Excel TRANSPOSE Function
  • How to use Excel OFFSET function
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation with conditional list
  • How To Create Drop-down List in Excel
  • Excel Data validation whole percentage only
  • Excel Data validation number multiple 100
  • Excel Data validation require unique number

How to extract word containing specific text in Excel

by

To extract a word that contains specific text,you can use a formula based on several functions, including TRIM, LEFT, SUBSTITUTE, MID, MAX, and REPT. You can use this formula to extract things like email addresses, or other substrings with a unique id.

Formula

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),
MAX(1,FIND("@",SUBSTITUTE
(A1," ",REPT(" ",99)))-50),99))

How to extract word containing specific text in Excel

Explanation

In the example shown, the formula in C5 is:

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",99)),MAX(1,FIND
("@",SUBSTITUTE(B5," ",REPT(" ",99)))-50),99))

How this formula works

The gist: this formula “floods” the space between words in a text string with a large number of spaces, finds and extracts the substring of interest, and uses the TRIM function to clean up the mess.

Working from the inside out, the original text in B5 is flooded with spaces using SUBSTITUTE:

SUBSTITUTE(B5," ",REPT(" ",99))

This replaces each single space with 99 spaces.

Note: 99 is just an arbitrary number that represents the longest word you need to extract.

Next, the FIND function locates the specific character (in this case, “@”) inside the flooded text:

FIND("@",SUBSTITUTE(B5," ",REPT(" ",99)))-50

FIND returns the position of the “@” in this text, from which 50 is subtracted. Subtracting 50 effectively “walks back” the position to someplace in the middle of the spaces preceding the substring of interest. In the example shown, the calculated position is 366.

The MAX function is used to handle the problem of the substring appearing first in the text. In that case, the position will be negative, and MAX is used to reset to 1.

With a starting positing established , MID is used to extract 99 characters of text, starting at 366 from the text in B5, again flooded with space:

MID(SUBSTITUTE(B5," ",REPT(" ",99)),366,99)

This extracts the substring of interest, with a lot of space characters before and after.

Finally, the TRIM function is used to trim leading and trailing space, and returns substring containing the special character.

Post navigation

Next Post:

Create One-dimensional and Two-dimensional Array

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel OR Function
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • How to use IFS function in Excel
  • Nested IF function example in Excel
  • IF with wildcards in Excel

Date Time

  • Get day name from date in Excel
  • Get first day of month in Excel
  • Convert Excel time to decimal seconds
  • How to calculate project start date based on end date in Excel
  • Excel Date & Time Functions Example

Grouping

  • Group numbers with VLOOKUP in Excel
  • Categorize text with keywords in Excel
  • Group times into 3 hour buckets in Excel
  • If cell contains one of many things in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to get amount with percentage in Excel
  • Subtotal by invoice number in Excel
  • How to count total columns in range in Excel
  • How to create dynamic named range with INDEX in Excel
  • With vs Without Array Formula in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning