number of characters in each cell

[jaywizz]

I am importing signals into some software using a CSV file. The signals can
only have a maximum of 32 characters in each cell. I have over 12000 signals
to import and there are at least 60 that are over the 32 character limit.

Is there anyway that I might be able to find these cells without having to
manually count the characters in each cell?

 

[Don]

try creating in another column next to the one you want to count the
charactors and put the formula =LEN(A1) where A1 is the first signal. Then
sort the sheet decending in the =LEN column and all the longer signals should
sort to the top

 

[Dave Peterson]

Maybe you could search using some wild cards:

Select the range (all the cells???) to search
Edit|Find
what: ????????????????????????????????*
(look in values if you've added formulas)
Find All (in later versions of excel)

That's 33 ?'s and and an asterisk.

 

[Rick Rothstein \(MVP - VB\)]

Or he could put this formula in the column next to the one with the
signals...

=IF(LEN(A1)>32,"<<==","")

Then double click the small, black square in the bottom right corner of the
cell in order to automatically copy it down through all 12000 cells... the
ones that are longer than 32 characters will be pointed at.

Rick

 

[Rick Rothstein \(MVP - VB\)]

What about using Conditional Formatting to let Excel show you which cells
have text greater than 32 characters in them. Select the entire column (that
contains your signal text) and then click Format/ConditionalFormatting from
Excel's menu bar. Select "Formula Is" from the first drop down, copy/paste
this formula into the 2nd field...

=LEN(A1)>32

where I have assumed your signal text is in Column A and that you selected
the entire column. Next, click the Format button, then the Pattern tab and
select a light (pastel) color from the chart. Finally, OK your way back to
the worksheet. All the cells contain signal text longer than 32 characters
will be highlighted in the color you selected.

Rick

 

[Dave Peterson]

If your data is spread over multiple columns, you could insert another worksheet
and fill it with a formula like:

=if(len(sheet1!a1)>32,1,"")
(Put this in A1 and drag to the right and down as far as necessary)

Then look for the 1's in that worksheet.

Select all the cells
edit|goto
special|Constants and only check numbers.

The cells in the same addresses will be offending cells in sheet1.