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 4. Total comments: 147

  1. Hi,

    I have looked at all of the above and I am still lost and can only find the outcome of one piece of the calculation

    =COUNTIF(O8:O17,"24+") etc

    I would like excel to count the number of times the Age Group occurs and also the corresponding value when it comes up in the total. For example:

    The below shows the number of times each age range is repeated, however, It does not match the number of Apps required. I need it to add the corresponding value.

    "No. of Apps
    required " Age Group
    1 16-18
    1 16-18
    1 16-18
    1 16-18
    1 16-18
    1 24+
    13 24+
    1 16-18
    1 18+
    1 19+
    1 16-18

    Total
    16-18 7
    18 1
    19+ 1
    24+ 2

    Apps req 23

    So the outcome I am hoping to achieve is the sum of all group matches the sum of apps req.

    Total
    16-18 7
    18 1
    19+ 1
    24+ 14

    Apps req 23

    Can you help?

    1. Hi Donaly,

      You should use the following formulas:
      =SUMPRODUCT((B1:B11="16-18") * A1:A11)
      =SUMPRODUCT((B1:B11="18") * A1:A11)
      =SUMPRODUCT((B1:B11="19+") * A1:A11)
      =SUMPRODUCT((B1:B11="24+") * A1:A11)
      If the "No. of Apps required" data are in B1:B11 and "Age Group" are in A1:A11.

  2. Hi

    I am trying to figure out how to combine a couple of countif's from two different columns to give me the answer in another cell.

    Eg:
    =COUNTIF(F5:F99; "*Red*") (so how many times"Red" is written in this particular series BUT i only want to know which "Red"s belong to a specific gender e.g this countif =COUNTIF(E5:E99; "*f*").

    I can't figure out how to combine them to prouduce a result that gives me all the 'f' in the columns E which are also "red"

    Would love your help.

    Thank you :)

  3. Vehicle Name Total KM
    Santro 15km
    Tavera 13km
    Swift 40km
    Indica 30km

  4. 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.

  5. Hi,
    I have a cells with numbers and characters i.e. 2C, 5B. Please could you tell me the calculation to count up just the numbers and then seperately the characters?
    Many thanks

  6. Hi
    successfully I can get the count of repeated text in a range for example: I have a repeated letter "P" in the range A21:D123 50 times the function I made = sum(len(A21:D123)this will count successfully all text in this range .

    I am struggling in counting the occurrences of different letters in the same range what I need is to count the "P" individually and at the same time if there are A, C and N I also need their numbers in the same range. how can I do this?
    thanks in advance

  7. Dear Sir,
    How to find out the position of a word in a text string in excel 2013.
    Example.
    Cell A1
    The cow has eaten the grass.
    Position of the word "cow" is 2.
    Position of the word "eaten" is 4.
    Position of the word "grass" is 6.
    Regards
    S.Narasimhan

  8. i have three option in A column present, leave , late i whant if present or leave = 0 and if late then 20 add total of month.

  9. 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.

  10. I have a excel of 4 columns and 90 rows, i want to compare this with another column having 180 rows and hence count the common in this 4 rows

  11. Hello!

    Just wanna how can I count a number which is double number, I have this numbers (1,2,3,4,5,11,23) and my problem was, the double number was counted separately. Instead of 7 it counted 9.

    Thank you very much!

  12. Some of the characters that have been written on the column if the column will come lekhata

  13. I Wish to get number value (example 5) in B1 cell from √ symbol in A1 cell

  14. formula worked fine but is not constant in the next cell below..... why is that. may I know how to let all the cells below function the same way I commanded the first cell above.

  15. if my cell is like below

    1,AF

    and I need to count " AF " is that possible to do? how? what formula will I have to use?

    Thank you.

  16. Hi guys,
    I have two columns in my spreadsheet - code(1-18) and duration in minutes (1-300)

    I need to get the sum of values(values in minutes) of the same code of cells. The code still in numbers (eg 1-18)

    Kindly someone help.

  17. I'm creating a spreadsheet (Excel 2003) in which a user enters data in several cells, each of which will permit only 50 numbers of characters (to include spaces). i have used data validation to limit cahracters to 50, but i want to show number of characters typed simultaneously. anyone please help me out. thanks in advance.

  18. 12251197-ltc-mun-transporte-ejecutivos-oriente-anibal-golindano-1900006354-m-i-swaco.xls
    12238385-ben-chalbi-faical-3028438-wireline.xls

    i want to find employee no which is stat with 1900 and does not start with 1900.
    how can i want find both in one formula.

  19. 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.

  20. Hi guys,

    I am using Excel 2010.

    I'd like to ask you for your kind support. I am looking for formula which calculate with text & no. (where no. is changing).

    Example:

    A1=R00000
    I need to get in A2=A1+1 --> R00001

    Thank you in advance.

    Milan

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

  22. I know how to count a "V" in a cell for one, but I was wondering if there is anyway I can count it as .5 and as 1 too. What can I put to make it count as .5?

  23. 843,138FS+5d,411,416,766,132
    41
    42
    43
    44
    83,81,45
    46
    70
    90SS
    399
    97
    98SS
    99SS
    100SS
    101SS
    130
    131
    834,134
    141
    544FF,531FF,558FF,761FF,560FF,570FF,555FF,551FF
    Each of these is in a separate cell, I'm trying to count those without SS or FF on the end. This is to count the number of FS relationships in an export from MS Project. I have been able to do the count of SS and FF.

    1. Hello, Carla,

      To help you better, we need a sample table with your data in Excel. You can email it to support@ablebits.com. Please add the link to this article and your comment number.

  24. hi,
    i want to enter a number and after that some information appear.for example when i enter 570, after that some information in a row about weight,quality,tonnage or other things should provide. i use the custom list in excel option but the error (cells without simple text were ignored) appear. what should i do???
    please help me
    thank you

  25. Where I work we have to maintain a daily census that lets us know how many people are in the building, including residents and guests.

    I want to know if I when a name is added or removed how can I keep a running total.

    So in other words to make sure I am explaining myself, if a resident moves in, or passes away, or is in the hospital or a family member stays with us we have to know how many people (not staff) or in the building. So if right now I have 70 residents and tomorrow one goes to the hospital and one moves out my total should say 68. So how to I get the 70 to change from 68 using names of people not numbers. Right now if someone forgets to change the total manually then we could have problems. But if the number changes with a name added or removed it would help a lot. Thank you.

  26. 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.
    Appreciate your help
    Thanks

  27. how to count only text.
    question
    1
    2
    d
    d
    e
    3
    2

    2
    d
    Now please count only text value. I tried but it counts space also.

  28. In an excel spreadsheet, if I have 5 cells in a row or column, 4 cells which have a number value and only one cell that has a text format, such as a name, how do I create a formula that will ignore cells with numbers and input the only text value as my answer?

    For instance,
    If C1 = 4, C2 = 6, C3 = 9, C4 = Quality Paving, C5 =2
    And I created a formula in another cell, such as Cell D14, with formula, =Sum(C1:C5), my value would be the sum of all the numbers. But I want my value in a certain cell to equal Quality Paving. What formula can you create that will ignore the number cells and give you the text value only as it is in another cell?

  29. 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)

  30. 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) ?

  31. 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

  32. 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



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)