Duplicate Characters in a cell

[Floydlevedale]

Hi all

I am not sure whether this can be done with a function/formula or with VBA, hence posting this query in general. A neat formula however would be the best solution, coz the number of entries varies, in the workbook attached I have 14 different cases though. I need to know when a cell does not repeat any of the possible 7 letters, in each given cell. If there is repitition then leave the cell blank , if all four of the letters are different then place "NR" in the cell. Please see attached wrkbk
for example.

Thanking you in advance

Floyd

[JBeaucaire]

This works for 4 digits, in B6, then copied down:

=IF(OR(ISNUMBER(SEARCH(LEFT(D6,1), MID(D6,2,3))), ISNUMBER(SEARCH(MID(D6,2,1), MID(D6,3,2))), MID(D6,3,1)=RIGHT(D6,1)), "", "NR")


Not sure about varying lengths.

[JBeaucaire]

Here's a formula that will work for any length string. Put this array formula in B6:

=IF(OR(ISNUMBER(SEARCH(MID(D6, ROW(INDIRECT("1:" & LEN(D6)-1)), 1), MID(D6, ROW(INDIRECT("2:" & LEN(D6))), LEN(D6))))), "", "NR")

...and confirm it by pressing CTRL-SHIFT-ENTER to activate the array. You will know an array is active when you see curly braces { } appear around your formula. If you just press ENTER the formula will always result in NR.

After entering the formula with CTRL-SHIFT-ENTER, copy downward.

[Floydlevedale]

Thanks a mill!! Always amazed at what you guys can do!

[newdoverman]

Using JBeaucaire's formula, I added to it to check that the characters in the list actually exist in the allowed characters. If there is a character in the string being checked that isn't allowed, and "illegal character" is returned.

=IFERROR(IF(AND((SEARCH(LEFT(D6,1),$D$2,1)>0),(SEARCH(MID(D6,2,1),$D$2,1)>0),(SEARCH(MID(D6,2,1),$D$2,1)>0),(SEARCH(MID(D6,3,1),$D$2,1)>0),(SEARCH(MID(D6,4,1),$D$2,1)>0)),IF(OR(ISNUMBER(SEARCH(LEFT(D6,1), MID(D6,2,3))), ISNUMBER(SEARCH(MID(D6,2,1), MID(D6,3,2))), MID(D6,3,1)=RIGHT(D6,1)), "", "NR")),"Illegal Character")

Hope this helps.

[JBeaucaire]

That was a good idea, NewDoverman.

Here's the same idea added to the better array formula from post #3. This is also an array:

=IF(OR(NOT(ISNUMBER(SEARCH(MID(D6, ROW(INDIRECT("1:" & LEN(D6))), 1), $D$2)))), "Illegal", IF(OR(ISNUMBER(SEARCH(MID(D6,1, 1), MID(D6,2,3))), ISNUMBER(SEARCH(MID(D6,2,1), MID(D6,3,2))), MID(D6,3,1)=RIGHT(D6,1)), "", "NR"))