Filtering for valid characters

[ElEdwards]

I use Excel spreadsheets for entering data for a character generator at my TV station (WKYC-TV, Cleveland). The result is a crawl of information at the bottom of the screen, like you see on CNN, FOX, etc.
Usually, this works with out a problem. The producers enter this crawl information into specific cells, which I have formatted to turn red if they enter more than 255 characters of information (mainly because that's about as much as a person can read before losing their place).
Here's the problem:
The character generator (by Chyron) accepts characters with a decimal value from 3 to 191 as valid characters. If the decimal value is higher, the data is interpreted as commands, ranging from changing the speed to terminating the crawl.
I want to filter each of these cells so that only acceptable characters are sent (decimal 3 to 191) and it must be done as soon as the producer hits Enter.
The range is F2:F500.

Thanks for any help

 

[Jim North]

Hi

In Excel there is a CLEAN function that eliminates all but printable characters.

If you are using VBA you can use the WORKSHEETFUNCTION to execute the CLEAN.

 

[Tom@CPC]

Are the acceptable characters entered into their own cell and then concanted (joined) to produce the scrolling message??? If so, you could try data validation for the cells that they enter the decimal characters into.

Now if they enter all of the characters into one cell, I suppose that you could do a text to column parse and validate the entries that way.

There are likely methods using code that could do this using an "OnEvent" code.

Hope this at least gets you started.

Tom

 

[Jim North]

It would help if I had a copy of your spreadsheet to test... but this is something I put together. Insert it the the code area of the sheet you want filtered:

<pre>
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

With ActiveCell
Select Case .Column
Case 6 'Column F
If Not (.HasFormula Or Len(Target.Value) = 0) Then
Target.Value = Application.WorksheetFunction.Clean(Target.Value)
End If
End Select
End With

End Sub

</pre>

 

[ElEdwards]

Jim,

After a little more investigation, I found that CLEAN won't work. The character code limitation (ASCII) is from 32 (space) to 125 (right bracket"}"). Any codes higher or lower are interpreted by the Chyron as commands, which I'm trying to prevent.
This spreadsheet is huge (7MEG) and I can't get it past the firewall here, otherwise I'd email it to you.
The cells where the data is entered are just cells... then other parts of the spreadsheet reference those entries, using UPPER and turning the cells red if there are more than 255 characters (CF).
Now I need to filter each one so that there is no character code below 32 or above 125.

Thanks!

 

[Jim North]

I'm a little confused (a somewhat normal state I must admit). Testing each character is possible, but in your first post you referenced 3 thru 190... are you now changing it to 32 thru 125?

 

[ElEdwards]

Yes, I was incorrect in the first post. The character limitation is between ASCII 32 and ASCII 125.

 

[Jim North]

Ok, again... insert this in the worksheet you want to be edited (is that one "t" or two??):

<pre>
Private Sub Worksheet_Change(ByVal Target As Range)

With ActiveCell
If .Column = 6 And .Row >= 2 And .Row <= 500 Then
NewText = ""
OldText = ActiveCell.Text
If Not (.HasFormula Or Len(OldText) = 0) Then
For i = 1 To Len(OldText)
If Asc(Mid(OldText, i, 1)) >= 32 And Asc(Mid(OldText, i, 1)) <= 132 Then
NewText = NewText + Mid(OldText, i, 1)
End If
Next i
ActiveCell.Value = NewText
End If
End If
End With
End Sub

</pre>

 

[ElEdwards]

OK, I entered that in the worksheet. It only worked once on one cell, then doesn't work again.
Any idea why?

 

[Jim North]

It should work on the range you provided...F2:F500. It only processes the cell you press enter from.... not all the cells in the range. If you want to do that it would need to be written as a subprocedure... is that what you are looking for?