Count 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 |
Count Characters
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). |
Count Characters
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! In article , 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? tks, steve |
Count Characters
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 "sjs" wrote in message ... 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 |
Count Characters
Excellent, thanks!!
"Harlan Grove" wrote: 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). |
Count Characters
Perfecto!! Thanks!
"Domenic" wrote: 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! In article , 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? tks, steve |
Count Characters
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 "Chris Lavender" wrote: 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 "sjs" wrote in message ... 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 |
All times are GMT +1. The time now is 04:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com