2.1 Count cells that start / end with a given string

a) Count all cells that start with C

  =COUNTIF(A1:A10,"c*")

c* is case insensitive. Hence, it will count cells starting with both c or C. Suppose you want to find all cells starting with Excel.

  =COUNTIF(A1:A10,"excel*")

b) Count all cells that end with C

  =COUNTIF(A1:A10,"*c")

c* is case insensitive. Hence, it will count cells starting with both c or C. Suppose you want to find all cells starting with Excel.

  =COUNTIF(A1:A10,"*excel")

2.2 Count no. of Cells that contain only non numeric values

Hence, if your cell is having a number 2.23, it will not be counted as it is a number. Use below formula considering your range is A1:A10

  =COUNTIF(A1:A10,"*")

2.3 No. of characters in a string excluding blanks

Say, you have a string like Arvind K. Patel and I need to know how many characters it has. In this case, it has 13 including decimal and leaving blanks aside. Use below formula for the same

  =LEN(SUBSTITUTE(A1," ",""))

2.4 No. of times a character appears in a string

Suppose you want to count the number of times, character “a” appears in a string

  =LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))

2.5 Count non numeric characters in a string

Suppose you have a string "abc123def45cd" and you want to count non numbers in this. If your string is in A1, use following formula in A1

  =IF(LEN(TRIM(A1))=0,0,SUMPRODUCT(--NOT(ISNUMBER((--
  MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))))

2.6 Count numeric characters in a string

Suppose you have a string "abc123def43cd" and you want to count numbers in this. If your string is in A1, use following formula

  =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,"")))
  OR
  =SUMPRODUCT(--ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))

2.7 Count alphabets in a string

Suppose you have a string "Ab?gh123def%h*" and you want to count only Aphabets. Suppose your string is in A1, put following formula for this.

  =SUMPRODUCT(LEN(A1)-
  LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),"")))
  OR
  =SUMPRODUCT(--(ABS(77.5-
  CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1))) < 13))

2.8 Most frequently occurring value in a range

Assuming, your range is A1:A10, enter the below formula as Array Formula i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula which you can see in Formula Bar. If you edit again, you will have to do CTRL+SHIFT+ENTER again. Dont put { } manually.

  =INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10),0))

The non-Array version of above formula

  =INDEX(A1:A10,MATCH(MAX(INDEX(COUNTIF(A1:A10,A1:A10),,)),INDEX(COUNTIF(A1:A10,A1:A10),,),0))

2.9 Extract First Name from Full Name

  =LEFT(A1,FIND(" ",A1&" ")-1)

2.10 Extract Last Name from Full Name

  =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))

2.11 Extract the Initial of Middle Name

Suppose, you have a name John Doe Smith and you want to show D as middle initial. Assuming, your data is in A1, you may use following formula

  =IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,1),"")

If name is of 2 or 1 words, the result will be blank. This works on 3 words name only as middle can be decided only for 3 words name.

2.12 Extract Middle Name from Full Name

  =IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND("
  ",A1)+1)),"")
  =IF(COUNTIF(A1,"* * *"),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1)),2),FIND("
  ",A1)+1,LEN(A1))),"")
  =IF(COUNTIF(A1,"* * *"),LEFT(REPLACE(A1,1,FIND(" ",A1),""),FIND("
  ",REPLACE(A1,1,FIND(" ",A1),""))-1))

2.13 Remove Middle Name in Full Name

  =IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1,"",REPT(" ",LEN(A1))),LEN(A1))),"")
  
  =IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND("",A1),""),"")

2.14 Reverse a String

Given a string like this in cell A1:="qwerty". The below formula will get you the reversed string.

  =TEXTJOIN(,,MID(A1,LEN(A1)-SEQUENCE(LEN(A1))+1,1))

  =TEXTJOIN(,,MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1))

2.15 Extract alphabets from a string

If your string is in cell A1 say "Muruk5esh N Ja7y3araj", use following formula to extract alphabets from this.

  =TEXTJOIN("",,IF(--(ABS(77.5-CODE(MID(UPPER(A1),SEQUENCE(LEN(A1)),1)))<13),MID(A1,SEQUENCE(LEN(A1)),1),""))
  OR
  =TEXTJOIN("",,IF(--(ABS(77.5-CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13),MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1),""))
  

2.16 Count the number of words in a cell / range

Suppose you have been given the following and you need to count the number of words in a cell or in a range.
Formula for calculating number of words in a cell

  =LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")

Formula for calculating number of words in a range

  =SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100),"",""))+(TRIM(A1:A100)<>""))

2.17 Extract user name from an e-mail id

Assuming A1 has a mail ID say A1:=s.muru19@gmail.com and you need to retrieve s.muru which is user name in the mail ID. Use following formula

  =IFERROR(LEFT(A1,SEARCH("@",A1)-1),"")

2.18 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,"")

2.19 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),"")

2.20 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),"")