Forum Discussion

ZEBALL07's avatar
ZEBALL07
Copper Contributor
Feb 13, 2024

Trying to count the locations in s cell subtracting spaces and commas. My formula isnt working.

Trying to count the locations in s cell subtracting spaces and commas. My formula isnt working. Below is the formula I am trying to use along with an example of the cell I am trying to count. 

 

=LEN(D2)-LEN(SUBSTITUTE(D2,”,”,””))+1

 

R604, R605, R606, R607, R608, R609, R610, R611, R612, R613, R614, R615, R625, R626, R627, R628, R629, R630, R631, R632, R633, R636, R637, R638, R639, R640, R641, R642, R643, R644, R645, R646, R647, R648, R649, R650, R651, R652, R653, R654, R655, R656, R657, R658, R659, R660, R661, R662, R663, R664, R665
excel
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 13, 2024

    ZEBALL07 

    Your formula works for me. Although, the text in your post is displaying a different kind of "" from what Excel is looking for with the syntax.

     

    Working formula:

     

    =LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1

     

     

    Another approach using 365 functions:

     

    =LET(terms, TEXTSPLIT(D2, ", "), COUNTA(terms))

     

Resources