How to not scramble punctuation symbols while scrambling words?

[Juggler_IN]

I am unable to retain the position of a punctuation in the text while performing Cambridge Transposition (which scrambles every word in the string except the first and last). The reference code is:

Sub v(strText)
    m = 1: Z = Split(strText, " "): j = UBound(Z)
    For u = 0 To j
        t = Split(StrConv(Z(u), 64), Chr(0)): w = UBound(t) - 1: l = Asc(t(w)): If l < 64 Or (l > 90 And l < 97) Or l > 122 Then e = t(w): w = w - 1 Else e = ""
        If w > 3 Then
            n = t(0): p = w - 1: S = ""
            For i = -p To -1
                S = t(-i) & S
            Next
            f = t(w)
            For p = 1 To p - 1
                r = Int((w - p) * Rnd()) + 1: n = n & Mid(S, r, 1): S = Left(S, r - 1) & Right(S, w - p - r)
            Next
            n = n & S
        Else
            n = Z(u): f = "": e = ""
        End If
        d = d & n & f & e & " "
    Next
    strText = d
End Sub
Sub Test()


    strTestString = "This is a test."
    v strTestString
    Debug.Print strTestString
    
    st = "According to a researcher at Cambridge University, it doesn't matter in what order the letters in a word are, the only important thing is that the first and last letter be at the right place. The rest can be a total mess and you can still read it without problem. This is because the human mind does not read every letter by itself but the word as a whole."
    v st
    Debug.Print strTestString


End Sub

 

[Rick Rothstein]

@DanteAmor,

The UDF is returning #VALUE ! error.

Two things...

1) Did you try the code I posted in Message #8 yet?

2) To help Dante out, show him the text that was being processed when that error occurred.

 

[DanteAmor]

@DanteAmor,

The UDF is returning #VALUE ! error.

Are you testing with the same text?
I have no problems.
Did you copy the entire macro?

 

[Juggler_IN]

I think it is on account of Excel version. I am using 2003 Excel. Giving an error at .RandBetween line.

 

[Juggler_IN]

@Rick,

Will Sentence Case function on the output work to preserve sentence case?

 

[Rick Rothstein]

@Rick,

Will Sentence Case function on the output work to preserve sentence case?

I have been thinking about this and there could be situations that code cannot handle well, so it would depend on what your text could look like. Right now, if the first word was "If", my code could change it to "fI"... fixing it at the beginning of the first word of text would be easy enough, but if your text could have multiple sentences in it, then abbreviations might be hard to handle. For example, if you this text...

"I went over to Dr. Mark Philips house last night. We had a good time."

There is no way (short of including a dictionary of abbreviations) that my code would be able to tell that the period after Dr. did not end a sentence (I am assuming we would want "rD." to remain that way instead of changing it to "Rd." should the randomizer decide to switch those two letters. Also the code would need to be able to tell that the "M" and "C" in something like "3M Company" should not change case if the randomizer leave them as they are (sentence case usually lower cases the characters after the first character in a sentence.

So, I am not sure how you would want to proceed with letter casing given your last request.

 

[Juggler_IN]

Fair point. I understand it.

 

[DanteAmor]

I think it is on account of Excel version. I am using 2003 Excel. Giving an error at .RandBetween line.

So you had not tried my first version or the one of CalcSux78

 

[Juggler_IN]

@Dante,

I did try, but is was also failing at "WorksheetFunction.RandBetween(1, Letters.Count)" line.

 

[shg]

I have a recollection that RandBetween is flaky in VBA in Excel 2003. Perhaps someone can confirm; I don't feel ambitious enough to test it.

 

[DanteAmor]

I have a recollection that RandBetween is flaky in VBA in Excel 2003. Perhaps someone can confirm; I don't feel ambitious enough to test it.

Sorry, I can not test in that version.

@Juggler_IN, I'm sorry, my design was based on that function, without it working, then you'll have to continue with Rick's good solution.