Problem with counting characters in a cell

[Guest]

Hi,

I'm trying to count the number of characters in a cell. At the moment i've
tried using =LEN(A2). This works fine when all characters are numeric, but
when there are letters in the mix, it shows the total number as 30 regardless
of whether there are 8, 12 or 14 characters in the code.

Someone please help!!!

 

[Guest]

There may really be 30 characters
If this is data which has come in from an outside sourse there may be spaces
try
=len(trim(A2))
You may have to modify how the cells are referenced or handeled if this is a
significant issue.

 

[Dave Peterson]

It should work ok with alph/numeric values.

I'd bet you have some extra spaces (or non-breaking spaces) in those cells.

=len(trim(substitute(a1,char(160)," ")))

might show you the problem.

If it turns out that you have spaces (or those non-breaking spaces), David
McRitchie has some code that will help clean this junk up:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm