Excel - Formulas on String and range

Excel Formula Tips and Tricks

Count Cells Starting (or Ending) with a Particular String

Count Cells Starting with a String

Say you want to count all cells starting with C:

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

Note: "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*")

Count Cells Ending with a String

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

Note: "*c" is case insensitive. Hence, it will count cells ending with both "c" or "C".

Suppose you want to find all cells ending with Excel:

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

Count Number of Cells Having Numbers Only

The COUNT function counts only cells that contain numbers.

=COUNT(A1:A10)

Reverse a String

Suppose cell A1 contains "qwerty" and you want to reverse it:

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

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

Reverse Number String

Suppose cell A1 has the number 16709 and you want to reverse it to get 90761:

=SUMPRODUCT(MID(A1,SEQUENCE(LEN(A1)),1)*10^SEQUENCE(LEN(A1))/10)

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

Reverse a String of Words

Suppose A2 has: "Moscow, London, Paris, Delhi, Washington, Miami, Detroit, Berlin"

You want: "Berlin, Detroit, Miami, Washington, Delhi, Paris, London, Moscow"

=TEXTJOIN(", ",SORTBY(FILTERXML("<t><s>"&SUBSTITUTE(A2,",","</s><s>")&"</s></t>","//s"),SEQUENCE(LEN(A2)-LEN(SUBSTITUTE(A2,","," "))+1),-1))

Reverse a Range of Cells

Suppose you have values in A2:A11 and want to reverse them in column D.

=INDEX(A2:A11,COUNTA(A2:A11)-SEQUENCE(COUNTA(A2:A11))+1)

=INDEX(A2:A11,COUNTA(A2:A11)-ROW(INDIRECT("1:"&COUNTA(A2:A11)))+1)

=SORTBY(FILTER(A2:A11,A2:A11<>""),SEQUENCE(COUNTA(A2:A11)),-1)

Get First Row Number in a Range

=MIN(ROW(D15:Q99))

Get Last Row Number in a Range

=@SORT(ROW(D15:Q99),,-1)

=MAX(ROW(D15:Q99))

Get First Column Number in a Range

=@COLUMN(D15:Q99)

=MIN(COLUMN(D15:Q99))

Get Last Column Number in a Range

=MAX(COLUMN(D15:Q99))

Extract Unique Characters

Suppose A1 contains "abracadabra" and you want the result as "abrcd":

=TEXTJOIN(,,UNIQUE(MID(A1,SEQUENCE(LEN(A1)),1)))

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

Test Whether a Range Contains Only Numbers

=ISNUMBER(--TEXTJOIN("",1,A1:A10))

Test Whether a Range Contains Only English Alphabets

=NOT(ISERROR(SUM(MATCH(UPPER(A1:A10),CHAR(ROW(65:90)),0))))

Ignore Blanks

=NOT(ISERROR(SUM(MATCH(UPPER(FILTER(A1:A10,A1:A10<>"")),CHAR(ROW(65:90)),0))))

Count Number of Cells Which Are Containing Only Characters

=COUNTIF(A1:A10,"*")

Number of Characters in a String Without Considering Blanks

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

Number of Times a Character Appears in a String

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

Count Non-Numbers in a String

Suppose your string in A1 is "abc123def45cd".

=COUNT(IF(ISNUMBER(--MID(A1,SEQUENCE(LEN(A1)),1)),"",0))-(A1="")

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

=SUMPRODUCT(--NOT(ISNUMBER(--MID(A1,SEQUENCE(LEN(A1)),1))))-(A1="")

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

Count Numbers 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:

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

Count only Alphabets in a String

Suppose you have a string "Ab?gh123def%h*" and you want to count only Alphabets.

Suppose your string is in A1, put following formula for this:

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

Generate English Alphabets

To generate English alphabets A...Z

=CHAR(SEQUENCE(26,,65))
=CHAR(ROW(65:90))

To generate English alphabets a...z

=CHAR(SEQUENCE(26,,97))
=CHAR(ROW(97:122))

To generate English alphabets in a row

=CHAR(SEQUENCE(,26,65))
=CHAR(SEQUENCE(,26,97))
=TRANSPOSE(CHAR(ROW(65:90)))