Counting Characters

To count all the characters in cell A1, use

=LEN(A1)

To count all the characters except lower case ’s’

=LEN(SUBSTITUTE(A1,”s”,”"))

To omit both lower and upper case S’s

=LEN(SUBSTITUTE(SUBSTITUTE(A1,”s”,”"),”S”,”"))

To count only lower case ’s’

=LEN(A1)-LEN(SUBSTITUTE(A1,”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.

7 Comments

  1. Stac:

    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.

    stac

  2. Kevin DeWhitt:

    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. stac:

    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.

    stac

  4. Kevin DeWhitt:

    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.

    Kevin

  5. Stac:

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

    Just a heads up.

    Stac

  6. Peroxe:

    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. Dave:

    in excel, the “SEARCH” function is *not* case sensitive, while the “FIND” function is.

Leave a comment