Counting Characters

To count all the characters in cell A1, use


To count all the characters except lower case ‘s’


To omit both lower and upper case S’s


To count only lower case ‘s’


I really wish Microsoft would have used SUB instead of SUBSTITUTE. I misspell that function about half the time I use it. Fat finger syndrome, I guess.

Posted in Uncategorized

10 thoughts on “Counting Characters

  1. I have a string of numbers — a UPS tracking number actually.

    The first two numbers are meaningless, but the next six are meaningful — they id the company making the shipment.

    I’ve been using a left,a1,8 and then right,a2,6 to get them out. Is there an easier way?

    Thanks for the help.


  2. Stac: Try using the MID function, as in =MID(cell address,3,6). This should return six characters, starting at position 3 (ignoring the first two, in other words). HTH

  3. Thanks, Kevin. It worked perfectly. Now if UPS will only give me the company names…

    They are charging our corp accounts, but I don’t know who they are. I just keep sending them to the rep, who passes them to someone else, who knows what they are doing. By the time I get the information back, it has been rinsed with a dummy filter and makes no sense…The joy of UPS. Thanks for the rant.


  4. I am familiar with UPS, having used their software for about 1 year to manage our shipping. Of course, anyone who knows the UPS “secret” (characters 3 – 8) can harvest your UPS account number and perform all sorts of havoc. It never happened to us, though. It would be nice to know who is doing it – either they are completely misinformed or are petty thieves…

    To get back on topic, the UPS software is based on MS Access, and can give you a direct dump to Excel of any field(s) you want. I made great use of this in my company. Look for “exporting data to excel” in the help files.


  5. Kevin–
    I sent you a email direct. I had a question that I didn’t want the world to see.

    Just a heads up.


  6. Thanks for hints on “To count only lower case ‘s'”. I would highly appreciate to have excel formula for “To count both lower case ‘s’ and upper case ‘S’.

  7. Hi I have to enter text into a column of cells in an excel sheet but up to a maximum of 30 characters in each cell, is there any way to get a live countdown of the characters used or left to use?? I can limit the number of characters using validation but dont know if I’ve over texted until I hit return or count every character as I’m typing and I have thousands to do!!! Ian

  8. Ian: Code won’t run while you’re editing a cell, so I don’t think there’s any way to do this.

    You could set your font to a monospace font, like Courier, and set your column width appropriately to tell you when you’re getting close.

  9. Thanks for that Dick I think that’ll work because the software this spreadsheet is uploaded to forces the font to the appropriate size it’s just that 30 characters is the limit. Thanks again Ian

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.