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

Data Analysis

  • How to do a t-Test in Excel?
  • Move chart to a separate worksheet in Excel
  • How to Create Gantt Chart in Excel
  • Reverse List in Excel
  • Subtotal function in Excel

References

  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Count unique text values with criteria
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to use Excel FORMULATEXT function
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation number multiple 100
  • Excel Data validation specific characters only
  • Excel Data validation date in next 30 days
  • Prevent invalid data entering in specific cells

Extract word that begins with specific character in Excel

by

To extract words that begin with a specific character, you can use a formula based on six functions: TRIM, LEFT, SUBSTITUTE, MID, LEN, and REPT. This approach is useful if you need to extract things like a Twitter user name from a cell that contains other text.

Formula

=TRIM(LEFT(SUBSTITUTE(MID(text,FIND("@",
txt),LEN(text))," ",REPT(" ",100)),100))

Note: 100 represents the longest word you expect to find that begins with the special character. Increase or decrease to suit your needs.

Explanation

In the example shown, the formula in C5 is:

=TRIM(LEFT(SUBSTITUTE(MID(B5,FIND("@",
B5),LEN(B5))," ",REPT(" ",100)),100))

How this formula works

Starting from the inside out, the MID function is used to extract all text after “@”:

MID(B5,FIND("@",B5),LEN(B5))

The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text. This is a bit sloppy, but it avoids having to calculate the exact number of characters to extract. MID doesn’t care if this number is bigger than the remaining characters,  it simply extracts all text following “@”.

Next, we “flood” the remaining text with space characters, by replacing any single space with 100 spaces using a combination of SUBSTITUTE and REPT:

SUBSTITUTE("@word and remaining 
text"," ",REPT(" ",100))

This seems crazy, but the logic becomes clear below.

Next, to extract just the word we want (i.e. @word), we use LEFT to extract the first 100 characters from the left. This gets us “@word”, plus many extra spaces. To visualize, the hyphens below represent spaces:

@word———————

Now we just need to remove all extra spaces. For that, we use the TRIM function.

 

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

  • How to use Excel XOR Function
  • OR function Examples in Excel
  • Invoice status with nested if in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel

Date Time

  • Get month name from date in Excel
  • Convert decimal seconds to Excel time
  • How to calculate future date say 6 months ahead in Excel
  • Extract time from a date and time in Excel
  • How to get Holiday Date from Year in Excel

Grouping

  • Group times into 3 hour buckets in Excel
  • How to randomly assign people to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Running count group by n size in Excel
  • Group numbers at uneven intervals in Excel

General

  • Excel Autofill Cell Ranges, Copy, Paste
  • Count cells that contain errors in Excel
  • How to generate random number weighted probability in Excel
  • Excel Default Templates
  • Freeze and Unfreeze Panes in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning