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)
'<i>qwer</i><b>1234</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 here:
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.
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 - <i>Guinness</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.