Random word order in Excel, 1 Cell

[bushidowarrior]

Hello all.

I think I have a dozy of an issue. i have been looking online with no anvil.

In A1 i have this text.

Hello world, how are you today.

I would like cell A2 to show:

world, / Hello / are / you / how / today.

Effectively a random word order. The space between the word will have a "/".

Is this possible? I would like to keep the sentence in 1 cell.

Thank you

 

[Peter_SSs]

Is it acceptable to use a macro?

 

[bushidowarrior]

Is it acceptable to use a macro?

If need be, of course... However, if it were possible without it great.

PS. If there is a use of a macro. It would go down and execute is per a row. e.g.

A1 (text) > A2 (results)
A2 (text) > A2 (results)
A3 (text) > A3 (results)
A4 (text) > A4 (results)
A5 (text) > A5 (results)
A6 (text) > A6 (results)

Thank you very much!!!!!!!

 

[Peter_SSs]

If need be, of course...

Then you could try this user-defined function. To implement ..
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.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function RandWords(s As String) As String
  Dim bits As Variant
  Dim tmp As String
  Dim i As Long, r As Long
  
  Randomize
  bits = Split(s)
  For i = 0 To UBound(bits)
    r = i + Int(Rnd * UBound(bits) - i)
    tmp = bits(i)
    bits(i) = bits(r)
    bits(r) = tmp
  Next i
  RandWords = Join(bits, " / ")
End Function

Excel Workbook
	A	B
1	Hello world, how are you today.	world, / are / you / Hello / today. / how
2	Here is another sentence	another / sentence / is / Here
3	Ron's cat is black	cat / black / is / Ron's
Random Sentence

 

[Rick Rothstein]

Give this UDF (user defined function a try)...

Function RandOrder(S As String) As String
  Dim Cnt As Long, RandomIndex As Long, Tmp As Variant, Arr As Variant
  Randomize
  Arr = Split(S)
  For Cnt = UBound(Arr) To 0 Step -1
    RandomIndex = Int((Cnt + 1) * Rnd)
    Tmp = Arr(RandomIndex)
    Arr(RandomIndex) = Arr(Cnt)
    Arr(Cnt) = Tmp
  Next
  RandOrder = Join(Arr, " / ")
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use RandOrder just like it was a built-in Excel function. For example,

=RandOrder(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peter_SSs]

This could theoretically fail on occasions but if you have the TEXTJOIN & CONCAT functions (Office 365) and can have a couple of helper columns (C:D for me & could be hidden) then I think this worksheet formula approach should work for up to 10 words most of the time. If it does fail, it will show an error, not an 'incorrect' mixed sentence.

All formulas copied down. C1 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

If you use this and are finding formula failures too often, that risk can be reduced by increasing the number of RANDBETWEENS in column D with a corresponding adjustment to the 56 values in column C. For example, if you double the RANDBETWEENS from 4 to 8, change the 56s in col C to 112 (& don't forget to re-confirm with Ctrl+Shift+Enter before copying down.)

Excel Workbook
	A	B	C	D
1	Hello world, how are you today.	today. / world, / how / are / Hello / you	5127390468	51273729575045225906984890477656001095340382808661850921
2	Here is another sentence	is / Here / sentence / another	6194580372	61964658061609855379122877318620061504689777592875974880
3	Ron's cat is black	Ron's / is / cat / black	4068725913	40468047255902169701013586218926961183941267298756308109
4	One two three four five six seven eight nine ten	nine / One / two / ten / four / seven / six / three / eight / five	8019365274	80191319318865920376036295111885902296123433295097928937
Random Sentence (2)

 

[bushidowarrior]

Thank you very much peter. Works perfectly! I had a look at your code, and I understand the logic. Very good!

 

[bushidowarrior]

After looking that is, a macro is a much easier approach! Even more so, is this function exclusive to office 360 as I only have 2016.

Thank you

 

[Peter_SSs]

Thank you very much peter. Works perfectly! I had a look at your code, and I understand the logic. Very good!

You're welcome.

.. is this function exclusive to office 360 as I only have 2016.

If you purchased stand-alone Excel 2016 you will not have the CONCAT and TEXTJOIN functions. If you purchased Excel 2016 through an Office 365 subscription then you will.