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

Data Analysis

  • How To Compare Two Lists in Excel
  • How to count table columns in Excel
  • Managing Conditional Formatting Rules in Excel
  • Reverse List in Excel
  • Conditional Formatting Rules in Excel

References

  • How to use Excel ROW Function
  • Excel Advanced Lookup using Index and Match Functions
  • Basic INDEX MATCH approximate in Excel
  • Perform case-sensitive Lookup in Excel
  • How to get relative row numbers in a range in Excel

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation don’t exceed total
  • Excel Data validation exists in list
  • Excel Data validation date in specific year
  • Excel Data validation whole percentage only

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.

Worked Example:   How to calculate Number of Instances in Excel

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.

Worked Example:   MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation

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.

Worked Example:   How to find and replace multiple values at same time in Excel

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

Previous Post:

Put names into proper case — Manipulating NAMES in Excel

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

  • IF, AND, OR and NOT Functions Examples in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • How to use IFS function in Excel
  • How to use Excel OR Function
  • Excel If, Nested If, And/Or Criteria Examples

Date Time

  • TODAY function: Description, Usage, Syntax, Examples and Explanation
  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • Get fiscal year from date in Excel
  • How to calculate next anniversary date or birthday in Excel
  • How to calculate next scheduled event in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign data to groups in Excel
  • Map text to numbers in Excel
  • If cell contains one of many things in Excel

General

  • AutoRecover file that was never saved in Excel
  • Convert column letter to number in Excel
  • Currency vs Accounting Format in Excel
  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • How to calculate percent of goal in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning