Filtering for valid characters

ElEdwards

Board Regular
Joined
Aug 29, 2002
Messages
220
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 :)
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

Board Regular
Joined
May 22, 2002
Messages
209
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

Board Regular
Joined
Aug 29, 2002
Messages
220

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

Board Regular
Joined
Aug 29, 2002
Messages
220

ADVERTISEMENT

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

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

Board Regular
Joined
Aug 29, 2002
Messages
220
OK, I entered that in the worksheet. It only worked once on one cell, then doesn't work again.
Any idea why?
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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?
 

Forum statistics

Threads
1,144,441
Messages
5,724,378
Members
422,547
Latest member
Vision1291

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top