» Altering results returned in case of an error.
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
Column A contains E-mail addresses, from which we would like to pull out the user names of Gmail users only (addresses containing ""@gmail"").
In case the address does belong to a gmail user, a string indicating that conclusion should be returned (instead of a #VALUE! Error).
Solution:
"Use the LEFT and FIND functions to find the requested substring in the address,
and the ISNUMBER and IF functions to determine the value to return in case the substring could not be found.
Following is the formula:
=IF(ISNUMBER(FIND(""@gmail"",A2)),LEFT(A2,FIND(""@gmail"",A2)-1),""Not a Gmail user"")
Example:
Emails_______________Gmail Users
User1@gmail.com______User1
User2@hotmail.com____Not a Gmail user
User3@yahoo.com______Not a Gmail user
User4@gmail.com______User4
Book Store:
Recommended Books:
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Mortgages For Dummies®
- 422 Tax Deductions for Businesses and Self-Employed Individuals : You Get a Raise Every Time You Find a Legitimate Tax Deduction
- Investing in Real Estate, Fourth Edition
- Microsoft Windows XP Inside Out
- The One Page Business Plan: Start With a Vision, Build a Company!
No comments have been submitted.
