Forum Discussion
count how many dashes in a cell
Hi, I hope you can help me create a formula that counts how many dashes are in a cell. For example, I might have a cell with "000-111-333-11" and a cell with "000-111-333." I am hoping there is a formula that could track that there are three dashes in the first cell and two in the second cell. Any suggestions?
Not sure if there is a better solution but one idea is to replace all dashes with nothing and then count the difference in length.
A1 contains text: formula =LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))
formula in german excel =LÄNGE(A1)-LÄNGE(WECHSELN(A1;"-";""))
Or the other way around using regular expression, replacing everything but dashes, then count length
=LEN(REGEXREPLACE(A1,"[^-]+","")) replace each non dash sequence
or =LEN(REGEXREPLACE(A1,"[^-]","")) replace each single non dash
SUBSTITUTE works since Excel 2016, REGEXREPLACE requires 365 or web version.You may try LEN() and SUBSTITUTE() functions.
=LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))
