Excel Formulas to Split Strings and Reverse Word Order

Reversing word order in Excel is a notoriously tedious task, often leaving data analysts struggling with clumsy manual workarounds for transposed names or inverted text strings. While traditional approaches rely on complex VBA macros or rigid, static Text-to-Columns features, modern Excel offers a far more elegant, formulaic path.

Utilizing dynamic arrays grants you immediate, non-destructive text manipulation that updates automatically. As a stipulation, this streamlined method requires Excel 365 or Excel 2021 to support functions like TEXTSPLIT, SEQUENCE, and TEXTJOIN. Below, we will dissect the exact formula construction and provide a step-by-step guide to reversing your string sequences seamlessly.

Data cleaning is one of the most common tasks performed in Microsoft Excel. Whether you are dealing with customer databases, product catalogs, or system logs, you will frequently find text formatted in ways that are not ideal for your current needs. One common scenario is needing to reverse the order of words in a text string-for example, converting "John Doe" to "Doe John," or changing a reverse-ordered product path like "Shoes Running Sports" to "Sports Running Shoes."

While Excel has long had functions to search and slice text, reversing word order historically required complex VBA macros. However, with the introduction of modern Excel functions (available in Microsoft 365 and Excel 2021), we can now construct elegant formulas to achieve this dynamically. In this comprehensive guide, we will explore multiple methods to split a string and reverse its word order, ranging from state-of-the-art dynamic array formulas to backward-compatible legacy formulas and Power Query solutions.

Method 1: The Modern Excel Way (Microsoft 365 & Excel 2021)

If you are using Microsoft 365 or Excel 2021, you have access to dynamic array functions that make this task incredibly straightforward. By combining TEXTSPLIT, SEQUENCE, CHOOSECOLS, and TEXTJOIN, we can split, reverse, and rebuild any text string without writing a single line of code.

The Formula

Assuming the text you want to reverse is in cell A2, copy and paste the following formula:

=LET(
    words, TEXTSPLIT(TRIM(A2), " "),
    num_words, COLUMNS(words),
    reversed_idx, SEQUENCE(1, num_words, num_words, -1),
    reversed_words, CHOOSECOLS(words, reversed_idx),
    TEXTJOIN(" ", TRUE, reversed_words)
)

How It Works step-by-Step

To understand why this formula is so powerful, let's break down the logic of the LET function, which allows us to define variables and make the formula highly readable:

• TRIM(A2): First, we clean up the text. TRIM removes any leading, trailing, or double spaces, ensuring our split operation doesn't create empty elements.
• words, TEXTSPLIT(...): The TEXTSPLIT function breaks the text into a horizontal array of individual words using the space character (" ") as the delimiter. For example, "Excel Is Fun" becomes {"Excel", "Is", "Fun"}.
• num_words, COLUMNS(words): We count how many words are in our array by measuring its columns. In our example, this returns 3.
• reversed_idx, SEQUENCE(1, num_words, num_words, -1): This is where the magic happens. The SEQUENCE function generates an array of numbers. Here, we tell it to generate an array of 1 row and 3 columns, starting at 3 (the total word count) and stepping backward by -1. This generates the array {3, 2, 1}.
• reversed_words, CHOOSECOLS(words, reversed_idx): CHOOSECOLS extracts specific columns from our original word array. Because we pass it our reversed index {3, 2, 1}, it pulls the 3rd word, then the 2nd, and finally the 1st. The array becomes {"Fun", "Is", "Excel"}.
• TEXTJOIN(" ", TRUE, reversed_words): Finally, we stitch the reversed array back together, separated by spaces. The TRUE argument tells the function to ignore any empty cells. The final output is "Fun Is Excel".

Method 2: Reversing a Simple Two-Word String (Universal Compatibility)

If you are working in an older version of Excel (such as Excel 2016 or 2019) or simply need a quick solution to swap a two-word name (e.g., "First Last" to "Last First"), you do not need dynamic arrays. A combination of classic text functions will do the trick.

The Formula

=MID(TRIM(A2), FIND(" ", TRIM(A2)) + 1, LEN(TRIM(A2))) & " " & LEFT(TRIM(A2), FIND(" ", TRIM(A2)) - 1)

How It Works

This formula works by locating the single space separating the two words and slicing the string around it:

• FIND(" ", TRIM(A2)): Finds the position of the space character. For "John Doe", the space is at position 5.
• MID(..., FIND(...) + 1, LEN(...)): Extracts everything after the space. Since it starts at position 6 (5 + 1) and runs for the length of the entire string, it safely captures "Doe".
• LEFT(..., FIND(...) - 1): Extracts everything to the left of the space, which captures "John".
• The & " " & operator joins "Doe" and "John" with a space in between, yielding "Doe John".

Note: This formula assumes exactly one space separator. If your cell contains three or more words (e.g., "John Fitzgerald Kennedy"), this simple formula will output "Fitzgerald Kennedy John", which only partially reverses the order.

Method 3: VBA User-Defined Function (For Older Excel Versions & Complex Strings)

If you are using Excel 2019 or older and need to reverse strings with a variable, unpredictable number of words, writing a quick User-Defined Function (UDF) in VBA is the cleanest and most reliable approach.

The VBA Code

To add this custom function to your workbook, follow these steps:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert > Module from the top menu.
3. Copy and paste the following code into the empty module window:

Function ReverseWords(txt As String) As String
    Dim arr() As String
    Dim i As Long
    Dim temp As String
    
    ' Trim duplicate spaces and split into an array
    arr = Split(Application.WorksheetFunction.Trim(txt), " ")
    
    ' Loop backwards through the array to build the string
    For i = UBound(arr) To LBound(arr) Step -1
        temp = temp & arr(i) & " "
    Next i
    
    ' Strip the trailing space and return result
    ReverseWords = Trim(temp)
End Function

Close the VBA editor. You can now use this brand-new function directly in your worksheet like any native Excel formula:

=ReverseWords(A2)

Why Use VBA?

The VBA approach is highly efficient because it handles any number of words natively using the Split and Join concepts. Additionally, it makes your spreadsheets easier to read for coworkers who might be intimidated by long, nested formulas.

Method 4: Power Query (The Ultimate No-Code Bulk Solution)

If you are working with large datasets containing thousands of rows, processing them with formulas can sometimes slow down Excel. Power Query is Excel's built-in data transformation tool, and it handles text parsing tasks effortlessly.

Steps to Reverse Word Order in Power Query

1. Select your data table, navigate to the Data tab, and click From Table/Range. This opens the Power Query Editor.
2. Navigate to the Add Column tab on the ribbon and click Custom Column.
3. Name your new column (e.g., Reversed Text).
4. In the custom column formula box, paste the following M-code formula (replace [YourColumnName] with the actual name of your text column):

   Text.Combine(List.Reverse(Text.Split(Text.Trim([YourColumnName]), " ")), " ")

5. Click OK.
6. Go to the Home tab and click Close & Load to return your reversed data back to a new Excel worksheet.

Understanding the Power Query M Code

The M formula executes the exact same logical steps as our Modern Excel formula, but in a functional, nested structure:

• Text.Trim([YourColumnName]): Removes unnecessary spacing.
• Text.Split(..., " "): Breaks the string into a list of individual words.
• List.Reverse(...): Flips the order of the items in that list.
• Text.Combine(..., " "): Joins the reversed list elements back into a single text string, separated by spaces.

Summary: Choosing the Right Method

To help you decide which solution is best for your current project, consult the table below:

Wrapping Up

Reversing word order in Excel doesn't have to be a painful task of manual typing or copy-pasting. If you are using Microsoft 365, the dynamic combination of TEXTSPLIT and CHOOSECOLS is your fastest, most flexible option. For legacy systems, simple formulas or a custom VBA function can get the job done without requiring an upgrade. Finally, for bulk data processing, look no further than Power Query to transform your columns in just a few clicks.