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 →

Table of contents

Comments page 2. Total comments: 147

  1. Thanks a ton for sharing this good info. If I can suggest, I'm likely not alone in finding the "Ultimate Suite" Ad with a scrolling gif hugely distracting. Someone obviously realized this and added a minimize function. Sadly, they only made the scrolling slightly smaller. This is why people use ad-blockers folks! :|

  2. HI,

    Going through you note helped me with working on a date wise schedule that spread across multiple rows which contained different programmes that are being aired. In this I had multiple advertisements placed which represented different product coded as A B and C etc. In come columns I had AD, and CA or any other combinations. As such i wanted to calculate all occurrences, and then create a summary of each representation.

    This note simply helped with solving my problem.

    Thank you and keep it up.

  3. Sir,
    Can excel show alphabet letter in against running number. Please explain formula.
    For example, for value (.001 to .999 = A)
    (1.00 to 1.99 = B)
    (2.00 to 2.999= C).
    Thanks!

  4. Greetings
    I am using this formula SUM(LEN(D21:D609)-LEN(SUBSTITUTE(D21:D609,"T","")))/LEN("T") as an array formula to count tears "T" recorded also using the same formula to count other quality faults such as bures "B"
    I now wish to record the time taken to repair the tear or bure in column D21:D609 by entering T14B15 effectively the tear took 14 minutes to repair and the Bure took 15 minutes to repair what must I do to count total tears and sum the total minutes to repair the tear or the Bure?
    Can you assist please

  5. 7411PG36353535 / 7411PG36353537 / 7411PG36353540 / 7411PG36353548 / 7411PG36354040 / 7411PG36354044 / 7411PG36354046 / 7411PG36354047 / 7411PG36354048 / 7411PG36355250 / 7411PG36355254 / 7411PG36355256 / 7411PG36357386
    the above alphanumeric series in one cell, if i have to count the alphanumeric series then what formula i have to use

  6. Hi,

    I need a formula to pick 5 reviewed cases from their production irrespect of the production numbers.

    Regards,
    Ravi

  7. How can I count an specific text that is marged with the few cells against a different column with the different texts.
    For example in column A I have a name called "Backshell" or in other cell I have "Footwell"(but these names are marged in the multiple cells. Say Installation marged from A2 to A5 and Footwell from A6 to A10) and In column C I have many texts or words such as "Installation" or "Design" but the cells are not marged; I want to count how many times Installation has been repeated for just backshell or just Footwell.

    Please help.
    Thank you.

  8. I have a problem that, i have different 100, 500 etc names
    Problem: #01
    i want to count duplicate name or same
    Problem # 02:
    Count its how much time in data
    Problem # 03:
    In same data reduced specific entry date wise
    PLZ Help Me Anyone.......

  9. Column Column Column
    A B C
    Karim 100 200
    Rohim 200 300
    Karim 100 500
    Rohim 500 100

    Result should be:

    Karim 900
    Rohim 1100

    I used = sumproduct(Vlookup (A1,A1:C4,{2,3},0))
    But it does not work. How can I do it? Pls help

  10. Hi Guys,

    I am not getting solve question that how will count this is.
    please find below this question.
    22pcs 5kg
    15pcs 8kg
    16pcs 10kg
    65pcs 12kg

    How will i count and sum please assist me.

    Thanks and Regards,
    Sujan

    1. Sujan:
      Enter "pcs" and "kg" in cells C62 and D62. These will be the headers.
      Enter the data in A48:A57. The formula is case sensitive so be sure the data matches the caps in the headers.
      In C63 enter =SUM(IF(ISNUMBER(FIND(C62,$A$48:$A$57)),VALUE(LEFT($A$48:$A$57,FIND(C62,$A$48:$A$57)-1)),0))
      then with the cursor in the formula bar in the formula click the CTRL Shift Enter keys at the same time. This is an array formula so you need to tell Excel to evaluate it as an array. When you enter the formula and then in the formula bar you put the cursor in the formula and click the CTL SHIFT ENTER keys it will put curly brackets around the formula which indicates to Excel that this is an array.
      When the value appears in C63 copy the formula over to D63.
      As you enter more data in the A range be sure to change the second cell address to match the last cell in the range. Right now the range is A48 to A57. If you add more data change the A57 to another cell address. Remember, there are three places in the formula for that range.

  11. Hi guys
    i have query about this question how to spit number and text as like:- rana123ranjan456 and 1234sohan567raj how to use split number and text.
    please assist me. please share this e-mail id abhisingh1111156@gmail.com

    Thanks and Regards,
    Sujan

  12. Thanks Doug.
    But is it possible to do sum without transferring text to the other column

    I don't want the result like below:
    20 cow
    60 Cow
    70 Cow
    150

    I want the result of sum like this:
    20Cow
    60Cow
    70Cow
    150Cow

    Pls help how can I do.

  13. I want to do sum for the following which contain number & Words

    20 cows
    60 Cows
    70 cows

    Result should be 150 or 150 cows.

    But if I use auto sum it does not work. How can I do it? Pls help me.

    1. Paul:
      Split the numbers from the words using Text-to-Columns.
      Highlight the cell, go to Data then choose Text-to-Columns and follow the prompts. After the words and numbers have been separated into their own cells you can use =Sum on the cells that contain numbers.

  14. sum samad26 karim26
    how I will sum? please answer?

  15. Hi,
    I have data numeric and text with numeric, pls help here how could i count how many times 123456 in data sheet.

    i hv use =SUMPRODUCT(--(LEFT(Sheet1!$G$2:$G$28069,6)=E4)) work ok, but when i was change formula =SUMPRODUCT(--((LEFT(Sheet1!G2:G28068,6)+0)=Sheet2!C3)) this was not working.

    123456-1234
    321654-3214
    456789-8579
    PO111991
    123456-1234

  16. Hello
    How do i count a reply yes for example for cells not arranged within a range
    E. G
    I created a checklist on excel and the reply to the answers yes/no as per the questions are not arranged in typical range

  17. Result analysis formular in excel

  18. i want to make result analysis in a way i want the number of As,Bs, Cs etc
    on a excel templete using formular.

    Best Regards
    NWANNA, D.

  19. i want to convert "PP" into numeric in attendance sheet
    example :- "P" count 1, if we insert double P into one Cell of MS Excel then count 2

  20. I am trying to count the number of names in a cell. What formula would I use to do this? The name format is first and last which would count as one name.

  21. Dear Natalia,
    I would like to clarify...I have a attendence sheet..it cotains Present,Sick leave and annual leave...I use countif formula to get total sick leave and annual leave.then got data.
    But when i filer the sheet based on user the total sick leave and annual leave are not changing.still showing same number...i am requsting to get a formula to solve this issue...

    When filter sheet by name result should be show only filtered user only...subtotal formula

    I hope clear my question...your quick reply highly appreciated

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  22. How can use countif function in subtotal formula...
    when i use subtotal function i couldnot find countif formula in the list...there is count and countA in the list...
    thank you in advance for your valuable support

  23. Dear support,
    I have big sheet.there is contain text id..when i filter sheet i cannot get total count of filtered text...

    1. Hello,

      If I understand your task correctly, please try the following formula:

      =SUBTOTAL(103,A2:A100)

      Hope it will help you.

  24. Hey I want to make shift schedule.. there are 40 members are there. I want to make sum for in any row or column if I will write down for example "m" i total how many "m" are there in row and column. please help me.

  25. Hi,

    I have data look like this below

    R0754117
    R0658417
    P0256413
    PX5698452
    SO2584696

    How do I do the countif wihtout the numeric? I just want to know how many count for R, P, PX and SO? Your help is appreciated. Thanks!

    1. FIRST APPLY THIS
      =LEFT(TEXT CELL,MIN(FIND({1,2,3,4,5,6,7,8,9,0},TEXT CELL&"1234567890")-1))
      IT'S GIVE U A NUMBER, AFTER THAT U WILL COPY THIS FORMULA AND PASTE IN THIS

      =SUBSTITUTE(TEXT CELL, LEFT(TEXT CELL,MIN(FIND({1,2,3,4,5,6,7,8,9,0},TEXT CELL&"1234567890"),"")

      I HOPE U WILL BE GET YOU ANSWER

    2. Hi, Amanda,

      if the data is situated in A1:A5, use the following formula to count the number of cells with R at the beginning of the cell:
      =COUNTIF(A1:A5,"R*")

      To count the rest of the values simply change R in the formula to other letters.

      You can read more about COUNTIF function on our blog post here.

  26. Hi,

    I’m working in one of the Trading Company, regularly need to prepare the delivery note report in Excel. Delivery note number is like D17/H0001. So can you please any one of them to provide code for add delivery note number +1.
    For example “D17/H00001”, Next row should be “D17/H00002”. I need the code only.
    Appreciate for your Help!

  27. I need help with using Excel 2010 on a formula that will display how many spaces and characters combined are in each cell with a text file. Your help is appreciated.

    Tammy

  28. hI,
    I have a workbook with 20 worksheets.In each worksheet I want to tally the amount of times the user selects from a drop down box of 10 options.I want to calculate the number of times each option is used on each worksheet and grand tally on the cover sheet.
    Please help

  29. How to Sum of this data

    4 hours
    8 hours
    3 hours
    6 hours
    =sum(?????

    1. Hello, Yogender,

      at first, please make sure that the Time format is applied to your values.
      Then, if the data starts in A2, use the following formula:
      =SUM(A2:A5)

      If the values are not in the cells use the following:
      =SUM(TIME(4,0,0),TIME(8,0,0),TIME(3,0,0),TIME(6,0,0))

  30. Im trying to count cells that contain numbers and text is this possible?
    I just want to get the sum of the numbers in each cell but there is text also in this cell.

  31. TOTAL MONTH OF PRESENT WORKERS FOR EX. P (1TO 15TH DAYS) & ABSENT A (16th to 31st)days in excel.

    i have any help for using the formulas in this cell sum total. separate total present days & total absent days

  32. I have a string of data, one number in its own cell which are mixed between numbers and H plus a number that we use for holidays on our timesheets, so may 6 H7 H7 7 8 8 How can I just record the numbers after the H so I can track the numbers of holidays being claimed?

  33. ANY HELP FOR USING FORMULA "COUNT IF" FOR SPECIFIED CELL WHICH CONTAIN MORE THAN 255 LETTER

  34. Can you help?
    I want to display the names of anyone with a "C" in a range of cells. So if any cells from A1:A10 has a "C" then A12 needs to display the name that is in cell A11, If there is not a "C" then no name will be displayed..

    Thank you!!

  35. I have past my data in one cell for E.g cell A2 1.aaaaa
    2.bbbbb
    3.ccccc
    I want to apply formula for calculate the no of Pointer mention in one cell can any one suggest me how to drived No. .....

  36. how to count different alphabet if many rows..
    example:

    H2=JLT
    I2=JLT
    J2=#N/A

    H3=PPR
    I3=PPR
    J3=#N/A

  37. Dear Svetlana,

    i have a small query, i have a attendance sheet like below:-

    Name Code 01-Dec 02-Dec 03-Dec 04-Dec 05-Dec 06-Dec 07-Dec Total C/L
    xxxx xx P 3/4 C/L P 1/2 C/L; 1/2 S/L C/L W/O P ?

    i want to count total C/L, but in this numaric data is there with C/L.
    pls suggest.

    thanks in advance.
    Rajesh

  38. Hi Maria,

    Hope you are doing well, I am a basic excel user, I am handling an excel work book in 2016 version, I've got a query as follows:

    I have 9 worksheets in an excel book and a specific column in each sheet which displays a series of reference numbers as:

    GAD5-CDC-T2-349-230315-DWG-PP-STR-0114-0
    GAD5-MGM-T2-349-230315-DWG-PP-STR-0114-0
    GAD5-CDC-T2-349-230315-DWG-SD-STR-0114-0
    GAD5-MGM-T2-363-250315-DWG-PP-STR-0119-0
    GAD5-CDC-T2-363-250315-DWG-DD-STR-0119-0
    GAD5-CDC-T2-363-250315-DWG-BD-STR-0119-0
    GAD5-CDC-T2-259-51-050515-DWG-FD-S-0233-00
    GAD5-CDC-T2-259-51-050515-DWG-TD-S-0233-00
    GAD5-MGM-T2-259-51-050515-DWG-LD-S-0233-00
    GAD5-MGM-T3-119-25-DDS-ST-1568-02
    GAD5-MGM-T3-119-25-DDS-RT-1568-02
    GAD5-MGM-T3-119-25-DDS-OT-1568-02

    and so on, Please note: these numbers are not following any sequence. I want to count the number of times the second last series of number is appearing. In this Instance

    0114 is 3 times
    0119 is 3 times
    0233 is 3 times &
    1568 is 3 times

    I want to count this by a formula and get the result in any other cell as a counter. I'll appreciate if you could help me in this please.

    1. Hi Syed,

      You should use the following formula to count all cells with "0114":
      =CONCATENATE("0114 is ", SUMPRODUCT((MID(A1:A12, FIND("-", A1:A12, LEN(A1:A12) - 8) + 1, 4) = "0114") * 1), " times")
      You can change this formula and use it to count cells with "0119", "0233", etc.

  39. hey i want to do the validation of PAN no. i.e. in between first five char are alfabets, next four are numeric & last one is also alfabet. its total 10 digit of no. so how can I check multiple PAN nos. at one tym. please tell me the formula.

    1. Hi dinesh,

      You should use the following formula:
      =AND(NOT(ISERROR(SUMPRODUCT(SEARCH(MID(LEFT(A1, 5),ROW(INDIRECT("1:"&LEN(LEFT(A1, 5)))),1),"abcdefghijklmnopqrstuvwxyz")))), NOT(ISERROR(VALUE(MID(A1, 6, 4)))), NOT(ISERROR(SUMPRODUCT(SEARCH(MID(RIGHT(A1, 1),ROW(INDIRECT("1:"&LEN(RIGHT(A1, 1)))),1),"abcdefghijklmnopqrstuvwxyz")))))

  40. Hello all, need a help i have text with drop down as follows in a cell
    5(Deliverables as per requirement)
    4(Deliverables with minor/cosmetic defects/bugs)
    3(Deliverables with minor/cosmetic defects/bugs but affected overall schedule)
    2(Deliverables with major defects/bugs)
    1(Deliverables with critical defects/bugs affected customer quality and schedule)
    i have different milestones(rows) with above drop down option now i want to sum all points for all rows.please let me know how SUM related cell consider above in numbers not text

    1. Hi Puran,

      You should use the following formulas:
      =SUMPRODUCT((LEFT($A$1:$A$15, 1)="5") * 1)
      =SUMPRODUCT((LEFT($A$1:$A$15, 1)="4") * 1)
      =SUMPRODUCT((LEFT($A$1:$A$15, 1)="3") * 1)
      =SUMPRODUCT((LEFT($A$1:$A$15, 1)="2") * 1)
      =SUMPRODUCT((LEFT($A$1:$A$15, 1)="1") * 1)
      Please replace "$A$1:$A$15" with your own range.

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

  42. 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 :)

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

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

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

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

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

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

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

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

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 :)