Let's say we are dealing with the following string entered in cell A1:
"Examples of replacing or counting any string elements in a cell"
To replace e.g. the 3rd letter 'a' with 'A' in the string, we would use this formula:
=SUBSTITUTE($A$1,"a","A",3)
To count e.g. the number of SPACES in the string, the following formula can be used:
=LEN($A$1) -LEN(SUBSTITUTE($A$1," ","")) (result = 10)
To count the number of substrings, such as e.g. "e", "in", "le" or "count" in the string, the following formulas can be used:
- =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"e","")))/LEN("e") (result = 6)
- =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"in","")))/LEN("in") (result = 4)
- =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"le","")))/LEN("le") (result = 2)
- =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"count","")))/LEN("count") (result = 1)
