I've been searching the archives but don't even know where to begin
because I don't know what function would be used in Excel to do this.
After some time, it just occurred to me that even conditional
formatting would work. An odd number of characters (I have code to
remove punctuation and spaces that I'd run on the text string first),
would make the cell turn red and even number of characters would turn
the cell background green because I need even-numbered text strings
and this would test for that before I go further.

I know something can be done because of this one message I managed to
find:
http://groups.google.ca/group/micros...a1f0de1cf496d2
but that's all there seems to be as Excel users are usu. concerned
with odd- and even-numbered rows rather than the length of a string of
text. tia! D

On Fri, 06 Apr 2007 18:01:06 -0400, StargateFan
<IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:

>I've been searching the archives but don't even know where to begin
>because I don't know what function would be used in Excel to do this.
>After some time, it just occurred to me that even conditional
>formatting would work. An odd number of characters (I have code to
>remove punctuation and spaces that I'd run on the text string first),
>would make the cell turn red and even number of characters would turn
>the cell background green because I need even-numbered text strings
>and this would test for that before I go further.
>
>I know something can be done because of this one message I managed to
>find:
>http://groups.google.ca/group/micros...a1f0de1cf496d2
>but that's all there seems to be as Excel users are usu. concerned
>with odd- and even-numbered rows rather than the length of a string of
>text. tia! D

p.s., it wouldn't have to necessarily be conditional formatting, nor
would it have to be changing the cell background colour, btw. That
was just a thought. Anything that indicates when a text string is an
even number of characters in length would be fine.

(Also, the example in the URL above deals with a macro. Hoping I
didn't confuse the issue with that.)

Thanks! D

Everything gets counted, including spaces and it requires
the Analysis TookPak be enabled in Tools | Add-ins...
=ISEVEN(LEN(B3))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"StargateFan"
<IDon'tAcceptSpam@IDon'tAcceptSpam.com>
wrote in message
On Fri, 06 Apr 2007 18:01:06 -0400, StargateFan
<IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:

p.s., it wouldn't have to necessarily be conditional formatting, nor
would it have to be changing the cell background colour, btw. That
was just a thought. Anything that indicates when a text string is an
even number of characters in length would be fine.

(Also, the example in the URL above deals with a macro. Hoping I
didn't confuse the issue with that.)
Thanks! D

On Fri, 6 Apr 2007 15:24:57 -0700, "Jim Cone" <(E-Mail Removed)>
wrote:

>Everything gets counted, including spaces and it requires
>the Analysis TookPak be enabled in Tools | Add-ins...
>=ISEVEN(LEN(B3))

Any way to do this without an addin?? Thanks.

Hi StargateFen,

Alternatively, without the ATp addin, the formula:

=MOD(LEN(A1),2)=0

will return True or False or even and odd length strings.

If you wish to use Conduitional formatting, use the above
formala for one conditiona and use the following formula
for a second condition:

=MOD(LEN(A1),2)=1 (odd)


---
Regards,
Norman


"StargateFan" <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote in message
news:(E-Mail Removed)...
> On Fri, 06 Apr 2007 18:01:06 -0400, StargateFan
> <IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:
>
>>I've been searching the archives but don't even know where to begin
>>because I don't know what function would be used in Excel to do this.
>>After some time, it just occurred to me that even conditional
>>formatting would work. An odd number of characters (I have code to
>>remove punctuation and spaces that I'd run on the text string first),
>>would make the cell turn red and even number of characters would turn
>>the cell background green because I need even-numbered text strings
>>and this would test for that before I go further.
>>
>>I know something can be done because of this one message I managed to
>>find:
>>http://groups.google.ca/group/micros...a1f0de1cf496d2
>>but that's all there seems to be as Excel users are usu. concerned
>>with odd- and even-numbered rows rather than the length of a string of
>>text. tia! D
>
> p.s., it wouldn't have to necessarily be conditional formatting, nor
> would it have to be changing the cell background colour, btw. That
> was just a thought. Anything that indicates when a text string is an
> even number of characters in length would be fine.
>
> (Also, the example in the URL above deals with a macro. Hoping I
> didn't confuse the issue with that.)
>
> Thanks! D
>

> will return True or False or even and odd length strings.

Should read:

will return True or False for even and odd length strings.



---
Regards,
Norman

=MOD(LEN(B3),2)=0
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"StargateFan"
<IDon'tAcceptSpam@IDon'tAcceptSpam.com>
wrote in message
On Fri, 6 Apr 2007 15:24:57 -0700, "Jim Cone" <(E-Mail Removed)>
wrote:

Any way to do this without an addin?? Thanks.

On Fri, 6 Apr 2007 23:39:41 +0100, "Norman Jones"
<(E-Mail Removed)> wrote:

>Hi StargateFen,
>
>Alternatively, without the ATp addin, the formula:
>
> =MOD(LEN(A1),2)=0
>
>will return True or False or even and odd length strings.
>
>If you wish to use Conduitional formatting, use the above
>formala for one conditiona and use the following formula
>for a second condition:
>
> =MOD(LEN(A1),2)=1 (odd)

[snip]

This works a treat. I'm in process of completing first job using this
spreadsheet. But I ran into a finetuning question.

Can we add text to the conditional formatting somehow? The archives
don't yield anything. What I was thinking is something like this as
an idea:


=MOD(LEN(A1),2)=0"text: This works. You can use."
=MOD(LEN(A1),2)=1"text: This is odd-numbered and won't work. Find
something else."

I just chose silly messages but I hope purpose is understood, that the
user is actually notified whether something will work or not. Thanks!

Hi StargateFan,

'-------------------
This works a treat. I'm in process of completing first job using this
spreadsheet. But I ran into a finetuning question.

Can we add text to the conditional formatting somehow? The archives
don't yield anything. What I was thinking is something like this as
an idea:


=MOD(LEN(A1),2)=0"text: This works. You can use."
=MOD(LEN(A1),2)=1"text: This is odd-numbered and won't work. Find
something else."

I just chose silly messages but I hope purpose is understood, that the
user is actually notified whether something will work or not. Thanks!
'-------------------

I do not think that you can use Conditional Formatting to
return a message.

I no nothing of your application, but perhaps you could use
the Worksheet_Change event to report the problematic string?

Perhaps, try something like:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Rng2 As Range
Dim rCell As Range
Dim CMT As Comment

Set Rng = Me.Range("A1:A100") '<<==== CHANGE
Set Rng2 = Intersect(Rng, Target)

If Not Rng2 Is Nothing Then
For Each rCell In Rng2.Cells
With rCell
On Error Resume Next
.Comment.Delete
On Error GoTo 0
If Len(.Value) Mod 2 = 1 Then
Set CMT = .AddComment
With CMT
.Text "This is odd-numbered and won't work." _
& vbLf & " Find something else."
.Visible = True
End With
End If
End With
Next rCell
End If

End Sub
'<<=============

This is worksheet event code and should be pasted into
the worksheets's code module (not a standard module
and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.


---
Regards,
Norman

> I no nothing of your application, but perhaps you could use

I know nothing of your application, but perhaps you could use


---
Regards,
Norman