« Password Characters
Replace97 »

Counting Characters

19th May 2004, 01:24 pm by Dick Kusleika

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.

Category: Worksheet Functions  |  Comment (RSS)

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

    19 May 2004, 3:02 pm

  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

    19 May 2004, 3:36 pm

  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

    20 May 2004, 12:54 pm

  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

    20 May 2004, 11:18 pm

  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

    21 May 2004, 8:23 am

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

    27 June 2007, 9:22 pm

  7. Dave:

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

    13 November 2008, 5:02 pm

Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

Leave a comment