Data Validation
 

Hi

I am using data validation in an Excel worksheet where I need to limit the
character length in particular cells to either 74 or 30 characters. In some
instances I am putting HTML coding in the cells as well but it does not need
to be counted within the character limitation, i.e. in some cells I can only
put 30 characters but if I insert the coding for italics <itext</i in the
cell as well it can be ignored. Is there anyway I can get the data validation
to ignore particular characters?

Any help greatly appreciated, Thanks

--
Scottie

Data Validation
 

I don't think so.

Maybe you could use a user defined function that parses the value in the cell
and returns the number you need. Then you can check that result.



Scottie wrote:

Hi

I am using data validation in an Excel worksheet where I need to limit the
character length in particular cells to either 74 or 30 characters. In some
instances I am putting HTML coding in the cells as well but it does not need
to be counted within the character limitation, i.e. in some cells I can only
put 30 characters but if I insert the coding for italics <itext</i in the
cell as well it can be ignored. Is there anyway I can get the data validation
to ignore particular characters?

Any help greatly appreciated, Thanks

--
Scottie

--

Dave Peterson

Data Validation
 

hi Dave

Thanks for the reply, I don't know how to do what you suggest and am
actually not sure if it would do what I need.

I am putting a string of text in a cell some of which may need to be HTML
coded for formatting and which must not exceed 30 characters, so for example
I could put in - Guinness is good for you - which would be 24 characters and
acceptable but because Guinness is a brand name it would require marking for
italicisation viz - <iGuinness</i is good for you - would take it to 31 and
my data validation would deem that unacceptable as it is more than 30. But
the HTML coding has not to be taken into consideration so to all intents and
purposes the latter slogan is still only 24 characters.

I therefore want to keep the data validation to prevent a text string going
above 30 but want to ignore the HTML formatting (e.g. <i etc) characters.

--
Scottie


"Dave Peterson" wrote:

I don't think so.

Maybe you could use a user defined function that parses the value in the cell
and returns the number you need. Then you can check that result.



Scottie wrote:

Hi

I am using data validation in an Excel worksheet where I need to limit the
character length in particular cells to either 74 or 30 characters. In some
instances I am putting HTML coding in the cells as well but it does not need
to be counted within the character limitation, i.e. in some cells I can only
put 30 characters but if I insert the coding for italics <itext</i in the
cell as well it can be ignored. Is there anyway I can get the data validation
to ignore particular characters?

Any help greatly appreciated, Thanks

--
Scottie

--

Dave Peterson

Data Validation
 

First, you're going to have to use a help cell (maybe in a column that's
hidden???).

And I don't know enough HTML to know all the strings, but maybe something like:

Option Explicit
Function NoHTML(str As String) As String
Dim iCtr As Long
Dim NewStr As String
Dim ThisChar As String
Dim InsideBrackets As Boolean

NewStr = ""
InsideBrackets = False
For iCtr = 1 To Len(str)
'<iqwer</i<b1234</b
ThisChar = Mid(str, iCtr, 1)
If ThisChar = "<" Then
InsideBrackets = True
End If
If InsideBrackets = True Then
'skip this character
Else
NewStr = NewStr & ThisChar
End If
If ThisChar = "" Then
InsideBrackets = False
End If
Next iCtr

NoHTML = NewStr

End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

========
Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type this in B1:
=NoHTML(a1)
Where A1 contains the value to check.

===========

Then you can use this in your data|validation rule for A1:
Custom:
and
Formula:
=len(b1)<=30

======
Another way that gives you an error indicator if the string is too long...

Drop the data|validation and use format|conditional formatting:
Formula is:
=len(nohtml(a1))30

format it as a red pattern and it'll warn you.




Scottie wrote:

hi Dave

Thanks for the reply, I don't know how to do what you suggest and am
actually not sure if it would do what I need.

I am putting a string of text in a cell some of which may need to be HTML
coded for formatting and which must not exceed 30 characters, so for example
I could put in - Guinness is good for you - which would be 24 characters and
acceptable but because Guinness is a brand name it would require marking for
italicisation viz - <iGuinness</i is good for you - would take it to 31 and
my data validation would deem that unacceptable as it is more than 30. But
the HTML coding has not to be taken into consideration so to all intents and
purposes the latter slogan is still only 24 characters.

I therefore want to keep the data validation to prevent a text string going
above 30 but want to ignore the HTML formatting (e.g. <i etc) characters.

--
Scottie

"Dave Peterson" wrote:

I don't think so.

Maybe you could use a user defined function that parses the value in the cell
and returns the number you need. Then you can check that result.



Scottie wrote:

Hi

I am using data validation in an Excel worksheet where I need to limit the
character length in particular cells to either 74 or 30 characters. In some
instances I am putting HTML coding in the cells as well but it does not need
to be counted within the character limitation, i.e. in some cells I can only
put 30 characters but if I insert the coding for italics <itext</i in the
cell as well it can be ignored. Is there anyway I can get the data validation
to ignore particular characters?

Any help greatly appreciated, Thanks

--
Scottie

--

Dave Peterson


--

Dave Peterson

Data Validation
 

Hi Dave

I'll give that a try. Thanks for your help.

All the best.

--
Scottie


"Dave Peterson" wrote:

First, you're going to have to use a help cell (maybe in a column that's
hidden???).

And I don't know enough HTML to know all the strings, but maybe something like:

Option Explicit
Function NoHTML(str As String) As String
Dim iCtr As Long
Dim NewStr As String
Dim ThisChar As String
Dim InsideBrackets As Boolean

NewStr = ""
InsideBrackets = False
For iCtr = 1 To Len(str)
'<iqwer</i<b1234</b
ThisChar = Mid(str, iCtr, 1)
If ThisChar = "<" Then
InsideBrackets = True
End If
If InsideBrackets = True Then
'skip this character
Else
NewStr = NewStr & ThisChar
End If
If ThisChar = "" Then
InsideBrackets = False
End If
Next iCtr

NoHTML = NewStr

End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

========
Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type this in B1:
=NoHTML(a1)
Where A1 contains the value to check.

===========

Then you can use this in your data|validation rule for A1:
Custom:
and
Formula:
=len(b1)<=30

======
Another way that gives you an error indicator if the string is too long...

Drop the data|validation and use format|conditional formatting:
Formula is:
=len(nohtml(a1))30

format it as a red pattern and it'll warn you.




Scottie wrote:

hi Dave

Thanks for the reply, I don't know how to do what you suggest and am
actually not sure if it would do what I need.

I am putting a string of text in a cell some of which may need to be HTML
coded for formatting and which must not exceed 30 characters, so for example
I could put in - Guinness is good for you - which would be 24 characters and
acceptable but because Guinness is a brand name it would require marking for
italicisation viz - <iGuinness</i is good for you - would take it to 31 and
my data validation would deem that unacceptable as it is more than 30. But
the HTML coding has not to be taken into consideration so to all intents and
purposes the latter slogan is still only 24 characters.

I therefore want to keep the data validation to prevent a text string going
above 30 but want to ignore the HTML formatting (e.g. <i etc) characters.

--
Scottie

"Dave Peterson" wrote:

I don't think so.

Maybe you could use a user defined function that parses the value in the cell
and returns the number you need. Then you can check that result.



Scottie wrote:

Hi

I am using data validation in an Excel worksheet where I need to limit the
character length in particular cells to either 74 or 30 characters. In some
instances I am putting HTML coding in the cells as well but it does not need
to be counted within the character limitation, i.e. in some cells I can only
put 30 characters but if I insert the coding for italics <itext</i in the
cell as well it can be ignored. Is there anyway I can get the data validation
to ignore particular characters?

Any help greatly appreciated, Thanks

--
Scottie

--

Dave Peterson


--

Dave Peterson