len function

[Guest]

I have a column of names consisting of first, middle initials(sometimes), and
last. The len function is part of the parsing formula I want to use. But I
find that in this case it always returns the number 39. This must be the
width of the column rather than the actual count of characters in the cell.
The data orginated in a database. How can I "clean up" my data in Excel so
that len returns the number of characters in the cell?

 

[Peo Sjoblom]

Probably either spaces or invisible html characters,
try

=LEN(TRIM(A1))

if it is still the same run this macro

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Guest]

Is your data padded with spaces to get the 39 characters? If so, then the
trim function should clear those. Ex, if your padded text is in A2, then
=trim(a2) should 'clean up' the entry.