Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Hyperlinks using R[1]C[1] and offset function in its cell referenc | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
Find a Function to use accross different worksheets | Excel Worksheet Functions |