 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / December 2005Count Characters
I have thousands of rows in my spreadsheet and I need to identify the cells that contain more than 30 characters. I know how to do a Validation on a cell but that only flags me when I enter data into the formatted cell, not when data already exists. Any ideas? tks, steve sjs wrote... >I have thousands of rows in my spreadsheet and I need to identify the cells >that contain more than 30 characters. I know how to do a Validation on a >cell but that only flags me when I enter data into the formatted cell, not >when data already exists. Any ideas? Strictly more than 30 characters? Edit > Find, Find what entry should be ??????????????????????????????? that's 31 question marks. Alternatively, use conditional formatting with the formula condition for cell D5 being =LEN(D5)>30 and give it a distinctive format (IMO, different background colors work best). Excellent, thanks!! > sjs wrote... > >I have thousands of rows in my spreadsheet and I need to identify the cells [quoted text clipped - 10 lines] > with the formula condition for cell D5 being =LEN(D5)>30 and give it a > distinctive format (IMO, different background colors work best). Assuming that Column A contains your data, enter the following formula in B1, and copy down: =(LEN(A1)>30)+0 The formula will return 0 for cells containing less than 30 characters, and 1 for those containing more than 30 characters. Hope this helps! > I have thousands of rows in my spreadsheet and I need to identify the cells > that contain more than 30 characters. I know how to do a Validation on a [quoted text clipped - 3 lines] > tks, > steve Perfecto!! Thanks! > Assuming that Column A contains your data, enter the following formula > in B1, and copy down: [quoted text clipped - 13 lines] > > tks, > > steve Hi Steve You can use an IF function on another sheet eg =IF(LEN(Sheet1!A1)>30,1,"") and then use that data somehow. You don't say what you want to do with your cells containing more than 30 characters. If you want to convert them all to 30 characters max, then you can enter a formula on Sheet2, eg =LEFT(Sheet1!A1,30), copy it down and across and then convert it all to values.... HTH Best rgds Chris Lav > I have thousands of rows in my spreadsheet and I need to identify the cells > that contain more than 30 characters. I know how to do a Validation on a [quoted text clipped - 3 lines] > tks, > steve Hi Chris, Thanks for the help...I simply need to identify fields that exceed 30 characters then I need to shorten them appropriately, the fields are product descriptions that I'll import into a business system. The "LEN" command to id the cells works like a champ! Have a great weekend! steve > Hi Steve > [quoted text clipped - 17 lines] > > tks, > > steve |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.