Skip to content
The Chat Archive
gpt-4o-mini-2024-07-18· OpenAI WildChat-4.8M (AI2)

Count all "CI" in cell, excel formula.

  1. User

    Count all "CI" in cell, excel formula.

  2. Assistant

    To count all occurrences of "CI" in a specific cell in Excel, you can use the following formula:

    =(LEN(A1)-LEN(SUBSTITUTE(A1,"CI","")))/LEN("CI")
    

    In this formula, replace A1 with the reference to the cell you want to analyze.

    Explanation:

    1. LEN(A1): This calculates the total length of the text in cell A1.
    2. SUBSTITUTE(A1,"CI",""): This replaces all occurrences of "CI" with an empty string, effectively removing them from the text.
    3. LEN(SUBSTITUTE(A1,"CI","")): This calculates the length of the text after removing "CI".
    4. The difference between the original length and the new length gives you the total number of characters removed.
    5. Dividing by LEN("CI") (which is 2) gives the count of occurrences of "CI" in the original text.

    Example:

    If cell A1 contains "CICICCI", the formula will return 3, indicating that "CI" appears three times in that cell.