Finding cells of different string length.

[kingie]

Hi,
I have a large table of data that is used to print random codes. The data is
started as an excel file which is then changed to notepad due to the large
quantity of codes. The ink jet printer that extracts the code uses the
string length to know where it starts and finishes each code. If there is a
cell with the wrong string length the whole thing goes out of sync. How can I
search the data for cells containing the incorrect string length.

 

[Fred Smith]

Where is the string length stored?

To find the length of a cell A1, for example, use =len(a1). Then compare
this to the string length.

Regards,
Fred.

 

[kingie]

The database i used was supplied in notepad form. It had 1000000 codes in it.
Each code was 8 characters long plus a space making each string length 9.
When the job went wrong and we had several thousand codes printed
incorrectly. I checked the dat and found that 4 of the codes were 8 character
without a space. To find them i copied and pasted all the data into an excel
worksheet. This gave me approximately 14 columns with 65536 cells in each. I
then set up a calculation for each cell =(lens A1)+1000000 Then dragged it
down and across to give a result for each cell. This all took hours. Then i
did a find on the result looking for cells with a value of 1000008 This gave
me my answer but didn't tell another cell only had 5 characters in it. So we
had another mishap whem printing the data. I am sure there must be an easier
way.
Thanks for your help.
Regards
charlie

 

[Mike]

This will look at Row 5 down to the last used row in column A

Sub stringLength()
Const whatColumn = "A" 'Change to your needs
Dim looper As Long 'looper = 5 Starting row of data change to your needs
Dim lastToCheckRow As Long
Dim cellPointer As Variant

lastToCheckRow = Range(whatColumn & Rows.Count).End(xlUp).Row

For looper = 5 To lastToCheckRow
Set cellPointer = Worksheets("Sheet1").Cells(looper, 1)
If Len(cellPointer) < 9 Then
MsgBox cellPointer.Address
End If
Next looper
End Sub

 

[kingie]

Hi Mike,
Sorry if I sound thick but that went straight over my head, any chance you
could tell me step by step where and what to put into the worksheet?
Thanks for answering
Regards
Charlie.

 

[Mike]

Sorry Charlie
But the fastest way to do this would be a macro. To test the code make a
copy of your workbook. Open the copy of your workbook. Hold down the Alt +
F11 keys this will take you to the vba part ofexcel. You will see a panel on
your left. You will also see something named VBAProject
(NameofYourWorkbook.xls). Right click on the bold print and select Insert
Module. Paste the code in the window to right. Hold down the Alt + F11 keys
again to return to excel. Hold down the Alt + F8 keys to bring up the macro
dialog box. You will see stringLength. Double click or highlight and select
run.
Sub stringLength()
Const whatColumn = "A" 'Change to your needs
Dim looper As Long 'looper = 5 Starting row of data change to your needs
Dim lastToCheckRow As Long
Dim cellPointer As Variant

lastToCheckRow = Range(whatColumn & Rows.Count).End(xlUp).Row

For looper = 5 To lastToCheckRow
Set cellPointer = Worksheets("Sheet1").Cells(looper, 1)
If Len(cellPointer) < 9 Then
MsgBox "Range(" & cellPointer.Address & ")" _
& "Text length is: " & Len(cellPointer)
End If
Next looper
End Sub