Note - 19 or 20 can be replaced with some IF condition to put in right 19 or 20 depending
upon the year. For example, year 82 is more likely to be in 20th century where year 15 is
more likely to be in 21st century.
81. Extract User Name from an E Mail ID
Assuming A1 has a mail ID say A1:[email protected] and you need to retrieve
v.a.verma which is user name in the mail ID. Use following formula –
=IFERROR(LEFT(A1,SEARCH("@",A1)-1),"")
82. Extract Domain Name from an E Mail ID
If you want to retrieve domain name which in above example is gmail.com, use following
formula –
=REPLACE(A1,1,SEARCH("@",A1)+1,"")
83. Location of First Number in a String
Suppose you have A1: = “abfg8ty#%473hj” and you want to find what is the position of first
number in this. In this string, first number is 8 and its position is 5. You can use following
formula -
=IFERROR(AGGREGATE(15,6,FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)
))),1),"")
84. Location of Last Number in a String
In the above example, last number is 3 and its position is 12. You can use following formula
to find this –
=IFERROR(AGGREGATE(14,6,FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)
))),1),"")
85. Find the Value of First Non Blank Cell in a Range.........................................................................
=IFERROR(INDEX(A1:A10,MATCH(TRUE,INDEX(NOT(ISBLANK(A1:A10)),,),0)),"").
86. Find First Numeric Value in a Range
=IFERROR(INDEX(A1:A100,MATCH(1,INDEX(--ISNUMBER(A1:A100),,),0)),””)
87. Find Last Numeric Value in a Range
=IFERROR(1/LOOKUP(2,1/A1:A100),””)
