len function
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? |
len function
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 -- Regards, Peo Sjoblom "ArthurJ" wrote in message ... 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? |
len function
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. "ArthurJ" wrote: 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? |
All times are GMT +1. The time now is 01:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com