Can I auto count the # of characters in an excel cell?

[Guest]

I'll appreciate any help I can get. I'm using MS Office Professional 2003.
I'm working in a worksheet that is used to download into a database. There
are rules defined for each type of data that is entered. The max number of
characters allowed in one particular cell is suppose to be 100. If you need
additional space, you would use the cell below for the carryover. The cell
allows me to exceed 100 without error or warning. However, once the info
downloads to the database and is extracted out again later, the cell has been
truncated to 100. Short of arrowing through all the data in the cell and
manually counting, is there a way to get the total count of characters
(including punctuation and spaces) in a cell for the times I know it could be
close? This is someone else's worsheet that I'm completing and all of the
formating is protected. Thanks in advance.

 

[Roger Govier]

Hi

Try the LEN function
=LEN(A1)
will return the number of characters and spaces in a cell

 

[Pete_UK]

The LEN() function will give you the number of characters in a cell,
eg:

=LEN(A1)

Hope this helps.

Pete

 

[Guest]

The Len fuction will give you the number of characters, but this might split
up a word if you arbitarily split up text after so many characters.

If Len(a1) returns >100 you need to split by words. The number of word is
calculated by:
=LEN(A9)-LEN(SUBSTITUTE(A9," ","")) You can use this to find the average
length of the text and use this as a guide.

Hope this helps
Peter

 

[Gord Dibben]

=LEN(cellref)


Gord Dibben MS Excel MVP

 

[MartinW]

Hi lcg,

Also
You can use Data Validation to stop this from happening.
Select your input cells
Go to Data>Validation
On the Allow dropdown choose Text Length
In the next dropdown select Less than or equal to
and in the Maximum Box type 100
And OK out

That will give you the default error alert
If you wish to change that click on the error alert Tab
before you OK out.

HTH
Martin

 

[Guest]

Thanks Roger. It doesn't get much easier than that.

 

[Guest]

Thanks Pete.

The LEN() function will give you the number of characters in a cell,
eg:

=LEN(A1)

Hope this helps.

Pete

 

[Guest]

Thanks Billy. I found I had 283 characters. To break it down in 100s I did
=left(a1,100) then =mid(a1,101,100). Thanks for the help.

 

[Guest]

Thanks for the help.

=LEN(cellref)


Gord Dibben MS Excel MVP

 

[Guest]

Data Valaidation is not available as part of the protection on this spread
sheet. But I can still use that if I copy my cell contents to a new sheet.
It's also good to know for the future. Thanks MartinW.