Thread: How to shuffle words in a row?

Is it possible to shuffle/reorder words randomly in a row? If i want to reorder randomly the words in the example .xlsx file how can i do this?

Thanks!

See if this workbook helps.

Put any value in a blank cell to force a calculation.
Delete that entry to shuffle again.

That's a bit more tricky, this workbook will shuffle up to 12 cells in a row.
If you have more than 12 cells in any row you should be able to stretch the tables "Random Numbers", "Rankings", "Cell Count", and "Column to Return" to suit your needs.
Hope this helps

I don't know what you mean by

Is it possible to do this with the merge function?

Do you mean CONCATENATE()?

If so you might need this UDF ConcRange()

See the two attached files

With merge function i mean in the home tab there is a button "merge & center" but it is for combining the words from all rows in one sentence. It is not what i need.

Thanks, for the formula ConcRange(). I will use it.

What i am trying to do is, for example, to paste 100 sentences in column A, then using "text to column" to separate the words in the sentences with the "space" delimiter". Then shuffle the words and joining them again in sentences. I found that i can do this faster with open office because it has an option to sort horizontally, but i will probably use excel because i have a problem with the formats in which open office safes the files.

The feature "merge & center" will not combine (concatenate) words/numbers from multiple cells.
It will only return what is in the first cell of the range and then merge the cells into one visuaully physical cell.

My advice would be to forget that this feature exists, it causes more problems than it's worth.

P.S.
Download the file for ConcRange() there are several worked example of its' use.

In Reply to your PM

You need to copy the VBa Function from "UDF Concatenate Range (ConcRange).xls" to your workbook.

To use this you need to do this.

1/. In Excel, with your workbook open, press Alt+f11 this will take you to the VBa Editor.

2/. With the "Insert" Tab choose "Module"

3/. In the resulting window paste all of the following code

Option Explicit

Function ConcRange(rng As Range, Optional Separator As String = "")
    Dim Cell As Range, Conc As String
    
    If WorksheetFunction.CountIf(rng, "") = rng.Cells.Count Then
        ConcRange = ""
        Exit Function
    End If
    For Each Cell In rng
        If Cell <> "" Then
            Conc = Conc & Separator & Cell
        End If
    Next
    If Separator <> "" Then
        Conc = Right(Conc, Len(Conc) - Len(Separator))
    End If
    ConcRange = Conc
End Function

4/. Save the file and close the VBa Editor.