Comments on: How to count the number of characters in Excel cells

Initially, Excel was designed to work with numbers. Fortunately, the developers of this helpful application didn't forget about text. Below, you'll find a number of formulas for counting characters in Excel. Just look through the examples and see which one best suits your needs. Continue reading →

Comments page 2. Total comments: 76

  1. Hi, I am trying to format a cell if it contains EXACTLY 17 characters. I input Vehicle VIN numbers which contain letters & numbers, 17 ONLY.

    I need the cell to change color IF it has 17 characters. (Cells A2 thru A29

    1. Hi ALLISON,

      Please try to do the following:
      1. Select cells A2:A29.
      3. Click Conditional Formatting -> New Rule.
      4. Select the "Use a formula to determine which cells to format".
      5. In the Formula field type the following formula:
      =LEN(A2)=17
      6. Click the Format button to set the format you need.
      7. Click OK.

  2. How can I count unique values among duplicates in a column? I have text that is duplicated on several rows. I need a total count of all text without counting duplicates.

  3. I have cells with part numbers, I.E.
    123456-1
    123456-2
    24W652-1
    24W652-2
    etc.
    I need formulas to find out how many odd numbers and even numbers I have total as they equate to left and right sides of an aircraft?

    1. Hello, Gary,

      Please use the following formula for the odd numbers:
      =SUM(--(MOD(1*RIGHT(A1:A7,1),2)=1))

      The one for the even numbers:
      =SUM(--(MOD(1*RIGHT(A1:A7,1),2)=0))

      Here A1:A7 is your list address. Please note that these formulas are array formulas. Use Ctrl + Shift + Enter to enter them.

  4. I'm struggling to count a specific alphabet like "P" in a cell range of a few words.

  5. Hi,
    Good information but i need additional help for filtered columns...

    I am trying to count the number of cells that have "X" (the X comes from a formula where the cell has this then an X goes into this cell (=IF(X1="LTI","X","")) in them but the column is filtered by year or month. So i only want to count the cells containing an X if they are displayed when filtered.

    really struggling with this.

    thanks

    1. Hello, Brad,

      Here's the fomula:
      =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)-MIN(ROW(A1:A10)),,1)),ISNUMBER(SEARCH("X",A1:A10)) + 0)

  6. hi,
    I have a one problem which I can't solve. I have about 10 sheets in one excel and in every sheet is an table which contain a rows with text. In the laste sheet I would like to summarize the whole from sheets but I don't know which formula I can use for do this. I want to count or sum the text accross the multiple sheets but I don't know how. Thanks for response!

    1. Hello, Valentina,

      You need to add the references to other sheets. For example, here the formula for the number of text cells from 3 sheets (Sheet1, Sheet2, Sheet3) with data in A1:A10:
      =COUNTIF(Sheet1!A1:A10, "*") + COUNTIF(Sheet2!A1:A10, "*") + COUNTIF(Sheet3!A1:A10, "*")

      1. Ms. Maria Azbel:

        What if you have a variety of names (instead of sheet 1, Sheet2)on your multiple sheets and you want to count or sum the text across those multiple sheets from one column; could you not use a formula that is like the First Last formula? =SUM(First:Last!D28)

        Would I do: =COUNTIF(First:Last!D28) ?

  7. hi
    would you please help me to find a equation in excel i have tried many times to find it i could not find. in a column there are different texts there are "/" in middle of some texts i want to avoid some letters from that texts that letters beyond the "/" and include "/" .i want to change below texts like
    eg: han/han han
    pan/san pan
    ran/man ran

    1. How do I separate the text for the following
      hienze/adsfdsaf kt/feD.xl - "i need "feD.xl" in this
      asda/sfer/aedfew/rwtw/sdfjhk.cl - "i need "sdfjhk.cl" in this"
      ewr/tye.tre - "i need "tye.tre" in this"
      Basically i need to get the text after the last "/" in every cell.
      Any help will be highly appreciated

      1. Whoops, I left out the piece before "SEARCH"...

        =RIGHT(A1,LEN(A1)-SEARCH("^",SUBSTITUTE(A1,"/","^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

      2. This should work. I've written it as though your data begins in cell A1. Copy this formula down.

        =SEARCH("^",SUBSTITUTE(A1,"/","^",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))

    2. Use this formula considering the values start from cell A1, B1 and so on:
      =LEFT(A1,FIND("/",A1)-1)

      This formula finds the "/" character and displays all the text till that character and the "-1" removes the "/" character

  8. Hi Maria I have tried your solutions but in all cases where im trying to count cells with text in the formulas seems to include cells with formulas in, I have 1400 rows and I want to count the number of cells that match my formula IF(ISERROR(MATCH *** . the formula im using works as it compares names in two columns but now I want to total it up and all i get using your ideas it is the full row total. Is there a way of doing this and ignoring cells with formula ?

    1. simple =len(text)

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)