Isogram Testing

[PaulGladis]

Hello all!
I would like to know if anyone help me with a formula that tests a word to see if it's an isogram - a word that has no repeating letters.

I've set up a 26 column test searching the word letter by letter and checking for the number of occurences of each of the 26 letters in the alphabet. This works fine for the English alphabet, but I've got some German, Polish, Czech and words from many other languages in the list that use characters that are not found in our alphabet.

Before I go plugging in more test columns for these characters (ñ, ç,[FONT=&quot] ä, ģ, Š, ǽ, etc), is there a way I can test for isograms with one single formula? And is it possible to make it NOT case sensitive - i.e. it counts 'c' and 'C' as two occurences and not one of each. Or should i just use the '=lower()' forumla to format the words beforehand?

I appreciate your expertise and hope there's some way we can accomplish this!

Thanks,
Paul
[/FONT]

 

[wigi]

Hi

I have not tested this extensively, but I think it will work:

=MIN(LEN(SUBSTITUTE(A1,MID(A1,ROW(INDIRECT("1:" & LEN(A1)));1),"")))=LEN(A1)-1

Confirm the formula with Ctrl-Shift-Enter, it is an array formula.

Isogram to be tested is in cell A1.

Wigi

 

[Peter_SSs]

Welcome to the MrExcel board!

From what I can see I think wigi's native Excel formula does the job and therefore should probably be used in preference to my User-Defined Function. However, since I was testing this problem with Regular Expressions I thought I would post my results anyway.

To implement my solution ..

1. Right click the sheet name tab and choose "View Code".

2. In the Visual Basic window use the menu to Insert|Module

3. Copy and Paste the code below into the main right hand pane that opens at step 2.

4. Close the Visual Basic window.

5. Enter the formula as shown in the screen shot below and copy down.

Note that special characters do not show up in my screen shot but the first ? in cell A5 is the ? character and the second ? in that cell and both ?s in cell A8 are ? characters.

Function Iso(s As String) As String
  Static RegEx As Object
  
  If RegEx Is Nothing Then
    Set RegEx = CreateObject("VBScript.RegExp")
    With RegEx
      .Global = True
      .Pattern = "(.).*\1"
    End With
  End If
  If Not RegEx.test(s) Then Iso = "Isogram"
End Function

Excel Workbook
	A	B	C
1		Peter_SSs	wigi
2	g	Isogram	TRUE
3	abcbdf		FALSE
4	try	Isogram	TRUE
5	12?3?45	Isogram	TRUE
6	1234528		FALSE
7	h5g39d/lk	Isogram	TRUE
8	?x?		FALSE
9	#$%^&*()poiu	Isogram	TRUE
Isogram

 

[Peter_SSs]

Actually, both of those are case sensitive. To fix mine, change this line

If Not RegEx.test(UCase(s)) Then Iso = "Isogram"

I think the fix for Wigi's is
=MIN(LEN(SUBSTITUTE(UPPER(A2),MID(UPPER(A2),ROW(INDIRECT("1:" & LEN(A2))),1),"")))=LEN(A2)-1
Don't forget to re-confirm with Ctrl+Shift+Enter, not just Enter

 

[pgc01]

Actually, both of those are case sensitive. To fix mine, change this line

If Not RegEx.test(UCase(s)) Then Iso = "Isogram"

Hi

Another option would be to specify that you don't want it to be case sensitive directly in the regex:

[FONT=Courier][COLOR=#00007f]Function[/COLOR] Iso(s [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]String[/COLOR]) [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]String[/COLOR]
    [COLOR=#00007f]Static[/COLOR] RegEx [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Object[/COLOR]
    
    [COLOR=#00007f]If[/COLOR] RegEx [COLOR=#00007f]Is[/COLOR] [COLOR=#00007f]Nothing[/COLOR] [COLOR=#00007f]Then[/COLOR]
        [COLOR=#00007f]Set[/COLOR] RegEx = CreateObject("VBScript.RegExp")
        [COLOR=#00007f]With[/COLOR] RegEx
            .Pattern = "(.).*\1"[/FONT]
[FONT=Courier]            .Global = [COLOR=#00007f]True[/COLOR]
            .IgnoreCase = True
        [COLOR=#00007f]End[/COLOR] [COLOR=#00007f]With[/COLOR]
    [COLOR=#00007f]End[/COLOR] [COLOR=#00007f]If[/COLOR]
    
    [COLOR=#00007f]If[/COLOR] [COLOR=#00007f]Not[/COLOR] RegEx.test(s) [COLOR=#00007f]Then[/COLOR] Iso = "Isogram"
[COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Function[/COLOR][/FONT]

 

[Peter_SSs]

Another option would be to specify that you don't want it to be case sensitive directly in the regex:

Ha, I'm still such a RegEx novice that I'd forgotten about that possibility.

But I'm still trying! And some of that other code structure I've used there would look familiar to you pgc.

 

[pgc01]

Ha, I'm still such a RegEx novice that I'd forgotten about that possibility.

But I'm still trying! And some of that other code structure I've used there would look familiar to you pgc.

Not any more, and yes, and thanks to you I've been posting it more frequently.

 

[PaulGladis]

You guys are rock stars! Thank you so much - it works like a charm, I couldn't have done it without your help.

Take care,
Paul

 

[Richard Schollar]

Here's another native formula option:

kloruklass.xlsm
	A	B
10	Count	TRUE
11	Summer	FALSE
12	Home	TRUE
13	because	FALSE
14	try	TRUE
15	random	TRUE
16	LOose	FALSE
Sheet2

Cell Formulas
Range	Formula
B10:B16	B10	=SUMPRODUCT((SEARCH(MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1),A10)<>ROW(INDIRECT("1:"&LEN(A10))))+0)=0

 

[Peter_SSs]

Here's another native formula option:

Richard, I don't know if it is likely with the OP's data but that one fails if the test cell contains a '?' or a '*' - a wildcard issue.

Edit: Or a '~'