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.
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
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
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
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 –
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
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
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’.
in excel, the “SEARCH” function is *not* case sensitive, while the “FIND” function is.
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
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.
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