data limiter - HELPPPPPPPPPPPPPP
Posted by Nadine Redd on June 29, 2001 12:58 PM
I have a customer that constantly is sending me files that the address cells contain more the the 35 characters that is allowed and then I haveto manually scroll the list 10,000+ to find the cells that have too many characters. Where can I go in Excel and tell it to look for the cells with morethe 35 characters and highlightthem so I do not have to rely visually. This is so time consuming. Thank You
Posted by IML on June 29, 2001 1:06 PM
You can highlight by going to your first cell and going to format, conditional formating
change the box to formula and put in the following
=LEN(a1)>35
assuming your address in A1. Now use the format painter to copy this to your whole column (make sure you do not use absolute ie $a$1)
A better alternative me be to add a column next to it and put the formala
=IF(LEN(A1)>35,1,0)
you could then sort by this and have all the one's you need to manually edit in one group.
good luck
Posted by Malc on June 29, 2001 1:10 PM
=if(len(A2)>=32,"Silly Customer","OK")
Use conditional formating in the format menu to highlight the Silly Customer cells red or you could sort the sheet to put all the silly customers at the top.
or use =left(A2,32) in the column next door and simply truncate anything over 32 characters
