Create a string of 6 random constonants - and ensure that none of these values are in a specified cell

ryanblything

New Member
Joined
Jun 27, 2018
Messages
2
Hi,

I'd like to create a string of 6 random consonants (e.g., "kftryp") but with 2 caveats:

1. no letter repeats (e.g., you can't have "kftryk")
2. the string does not contain a consonant that is used in another specified cell (e.g., if the specified cell - A1 - contains "tiger" then "kftryp" is not suitable because it contains a "t" (so I need code that says "give me 6 unique consonants, and don't give me a consonant that is contained in e.g., cell A1).

I've figured out how to create a string of random consonants:

=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)&
=MID("BCDFGHJKLMNPQRSTVWXYZ",RANDBETWEEN(1,21),1)

but I don't know how to achieve the 2 caveats - any suggestions would be very much appreciated!
Thanks,
Ryan
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,736
Are you willing to consider a VBA solution? If yes, do all the letters have to be lower case as in your "kftryp" example? If not, is "kftryK" acceptable?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,736
Getting close to bedtime so I'll assume you want to deal only with lower case consonants. Here's a user-defined function (UDF) you can try. Once installed, you can use it just like a worksheet function as in the example below where the Exclude range is cell A1 holding a value "tiger" and the consonant string contains 6 letters (the numChars argument). Below the example are the installation instructions and the UDF.
Excel Workbook
AB
1tigerfzdxnk
Sheet7


To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Function RandLet(numChars As Integer, Exclude As Range) As String
'Assume want only lower case consonants
Dim d As Object
Dim Vowels As Variant
Dim Lets As String
Dim FinalResult As String
Application.Volatile
Set d = CreateObject("Scripting.dictionary")
For i = 1 To Len(Exclude.Value)
    x = d.Item(LCase(Mid(Exclude.Value, i, 1)))
Next i
'no vowels wanted
Vowels = Array("a", "e", "i", "o", "u")
For i = LBound(Vowels) To UBound(Vowels)
    x = d.Item(Vowels(i))
Next i
Do
    Lets = Chr(Int((26 * Rnd) + 97))  'generates a-z
    If Not d.exists(Lets) Then
        d.Add Lets, d.Count + 1
        FinalResult = FinalResult & Lets
        Lets = ""
    End If
Loop Until Len(FinalResult) >= numChars
RandLet = FinalResult
End Function
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,943
Office Version
2016
Platform
Windows
Here's an alternative function :
Code:
Function RandomString(Exclude As Range) As String
Dim a$, c%, x%, s$, rs$
a = "bcdfghjklmnpqrstvwxyz"
For c = 1 To Len(Exclude)
    a = Replace(a, Mid(LCase(Exclude), c, 1), "")
Next
For x = 1 To 6
    s = Mid(a, WorksheetFunction.RandBetween(1, Len(a)), 1)
    a = Replace(a, s, "")
    rs = rs & s
Next
RandomString = rs
End Function
 
Last edited:

ryanblything

New Member
Joined
Jun 27, 2018
Messages
2
Many thanks, Joe! That worked perfectly!
Ryan
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,943
Office Version
2016
Platform
Windows
I think the code I posted is more efficient.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,624
Messages
5,445,563
Members
405,339
Latest member
dayanand008

This Week's Hot Topics

Top