How to scramble letters in words in Excel?
In some situations, it may be necessary to scramble the letters within words in an Excel worksheet. For example, you might need to anonymize data, create word games, or prepare exercises for teaching and testing purposes. As shown in the screenshot below, you have a list of words, and the goal is to randomly shuffle the individual letters of each word. The following article explores several effective methods to achieve this task, catering to different needs, Excel versions, and user skill levels.
Scramble letters in words with User-defined function
Easily scramble letters in words with Kutools for Excel
Scramble letters in words with Excel formula (Excel 365 and later)
Scramble letters in words with User-defined function
One way to scramble the letters of words in Excel is to use a User-defined function (UDF) written in VBA. This approach is practical for users who have access to Excel’s built-in VBA editor and are comfortable working with custom functions. The VBA solution is advantageous because it works across almost all versions of Excel and can be easily reused for different datasets.
Caution: Macros must be enabled for this method. Always save your work before running or editing VBA code, and be sure to only use VBA code from trusted sources to avoid potential security issues.
To scramble the letters within cells using a custom function, follow these steps:
1. Press the Alt + F11 keys at the same time to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then, copy and paste the VBA code shown below into the opened Code window. See the screenshot for reference:
VBA code: Scramble letters in words
Function Scramble(oldname)
On Error Resume Next
n = Len(oldname)
newname = ""
Do
i = Int(Rnd() * n) + 1
c = Mid(oldname, i, 1)
If c <> "*" Then
newname = newname & c
oldname = Replace(oldname, c, "*", , 1)
End If
Loop Until Len(newname) = n
Scramble = LCase(newname)
End Function
3. After you paste the code, press Alt + Q to close the VBA editor and return to your Excel worksheet.
4. Select a blank cell in your worksheet where you want the scrambled word to appear, then enter the following formula in the formula bar. For example, if the word you want to shuffle is in cell A2, use:
=scramble(A2)
Press Enter. To scramble other words in the list, click to select the result cell, and drag its Fill Handle down to automatically fill the formula for the remaining cells. The result will be a randomly scrambled version of each word. Refer to the screenshot for how the scrambled words appear in Excel:
Note: In the formula, A2 refers to the original word you wish to shuffle. If your data is in a different column or row, update the cell reference accordingly. The function will re-scramble the word each time the worksheet recalculates, so if you want the results to remain fixed, you may want to copy the results and paste them as values.
Pros: Works in most Excel versions, customizable for more complex needs.
Cons: Requires enabling macros, VBA familiarity, and might not be permitted in organizations with strict security policies.
Easily scramble letters in words with Kutools for Excel
If you prefer a straightforward, code-free solution, Kutools for Excel provides a practical utility for manipulating text in cells. While its Reverse Text Order feature is not a pure letter shuffler, it can quickly rearrange the order of letters in any selection, which can be useful for basic scrambling needs or for generating alternate text patterns for data testing or educational exercises.
1. First, select the range of cells containing the words you want to rearrange. Then, go to the Kutools tab, click Text, and choose Reverse Text Order, as demonstrated below:
2. In the Reverse Text dialog box, specify the separator you want to use to define what gets reversed. For scrambling individual letters, choose the None option, and then click OK.
The selected words are now rearranged by reversing the letter order, as shown in the screenshot below. You can undo the operation by pressing Ctrl+Z if you want to try again or change the separator option.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Pros: User-friendly and does not require any coding, perfect for quick text manipulation. Compatible even with users who lack formula or VBA experience.
Cons: Primarily reverses letter order instead of providing a truly randomized scramble, and the add-in needs to be purchased and installed.
Scramble letters in words with Excel formula (Excel 365 and later)
Excel 365 and later versions include powerful dynamic array functions such as SORTBY and RANDARRAY. These can be combined with MID and SEQUENCE to randomly shuffle the letters in a word, making it possible to construct a formula-based scrambling solution without any add-ins or VBA code. This method is suitable for modern Excel users who need dynamic, formula-driven randomization of text within individual cells.
This approach works best when you want to create a reproducible and fully transparent scrambler with helper columns. Here's how you can do it:
1. Suppose your original word is in cell A2. Enter the following formula into cell B2:
=TEXTJOIN("", TRUE, SORTBY(MID(A2, SEQUENCE(LEN(A2)),1), RANDARRAY(LEN(A2))))2. Press Enter. The scrambled version of the word in A2 will instantly appear in B2. To apply this to other cells in column A, select cell B2, use the Fill Handle, and drag it down over the range where you want to scramble more words.
Parameter explanations:
- MID(A2, SEQUENCE(LEN(A2)),1): splits the word into individual letters.
- RANDARRAY(LEN(A2)): generates a random number for each letter to enable shuffling.
- SORTBY(..., RANDARRAY(...)): sorts the letters based on the random numbers to shuffle them.
- TEXTJOIN("", TRUE, ...): joins the shuffled letters back into a single word.
Tips: Every time the worksheet recalculates, the scrambled result may change since it depends on randomly generated numbers. If you want static results, copy the output cells and paste them as values. If you see a #NAME? error, check that you are using Excel 365 or a version that supports SORTBY and RANDARRAY.
Pros: Code-free, compatible with any spreadsheet that supports dynamic arrays, and outputs a different scramble with each calculation.
Cons: Only available in Excel 365 and later; cannot be used in older versions without similar dynamic array support.
Troubleshooting suggestions: If the result doesn't update, make sure calculation mode is set to automatic (Formulas > Calculation Options > Automatic). Double check for formula typos, and confirm the output cells are formatted as General or Text. If the scrambled words do not appear as expected, clear any filters or data validation that might block formula updates.
Summary tips: For most users, the formula method offers a quick and effective way to scramble letters if you're using a recent version of Excel. If you require fixed scrambled versions or need to support older versions, consider using the VBA method or Kutools as alternatives.
Related articles:
- How to conditional format cells based on first letter/character in Excel?
- How to capitalize all letters in cell or a column in Excel?
- How to filter values by first letter or last character in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in