Random Replace Characters in a Word

[The_Rock]

Hi Folks
I've been scouring the net but without success. I'm hoping the genius's here will be able to help me

I'm trying to mock up some work for my daughter to help her with Synonyms and Antonyms.
What I would like to do, if possible is:

Column D looks at Column F (which will be hidden) and will randomly substitute some of the letters with an underscore.
In addition, Col D would have to space out the letters else all the underscores would look like a continuous line.
I'd like for the substitutions to be random, as I may give her the same words again at a later date but would like different letters to be missing.

I hope this makes sense in what I'm trying to achieve.
I assume it maybe a UDF that is needed but if you can help, that'll be very much appreciated.

Thank You

 

[steve the fish]

The problem with randomising this is that you will end up with no letters and 6 dashes _ _ _ _ _ _ eventually. Not much use to you that i presume? What is the minimum number of letters that need to remain?

 

[The_Rock]

Thanks for replying Steve. I understand where you're coming from. I'm not sure if its possible, but I thought I'd check.
I think minimum of 3 letters should be sufficient. It won't be complex long words.

 

[Peter_SSs]

Give this UDF a try. You can adjust the (approximate) proportion of blanks with the 'Const' line in the code.

Function SubLetters(s As String) As String
  Dim i As Long, x As Long
  Dim Positions As Variant

  Const PercentageReplaced As Double = 0.5 '<- Adjust # of blanks with this between 0 & 1

  Randomize
  Positions = Split("#" & Join(Application.Transpose(Evaluate("row(1:" & Len(s) & ")")), "# #") & "#")
  For i = 1 To Len(s) * PercentageReplaced
    x = Replace(Positions(Int(Rnd * UBound(Positions))), "#", "")
    Mid(s, x, 1) = "_"
    Positions = Filter(Positions, "#" & x & "#", False)
  Next i
  SubLetters = Replace(s, "_", " _ ")
End Function

Example output

Book1
	B	C	D	E	F
2	import		_ _ po _ t		export
3	cellar		a _ t _ c		attic
4	simple		_ omp _ _ _ _ t _ d		complicated
Sub letters

Cell Formulas
Range	Formula
D2:D4	D2	=subletters(F2)

 

[The_Rock]

Wow - thank you so much Peter. That is awesome - just what I needed!!
As I said there are real genius's in this forum

 

[Peter_SSs]

You're welcome. Thanks for the follow-up - and the kind words.