How to Reverse Order of a Comma-Separated String in Excel

Reverse the order of a comma-separated string in Excel using the simple and intuitive methods mentioned in this Excel tutorial.

Do you manually rearrange comma-separated strings in Excel? Well, you’re in luck! In this blog, I’m going to explain several methods of reversing the order of a comma-separated string in Excel.

Whether you’re dealing with a list of names, a series of values, or any other data, learning this handy trick will save you valuable time and effort. No more struggling with multi-step formulas or tedious copy-paste operations.

With just a few simple steps, you’ll be able to effortlessly flip the order of your strings, unlocking a whole new level of data manipulation prowess. Let’s get started!

Also read: How To Reverse Order In Excel

Reasons to Reverse Order of a Comma-Separated String

Here’s why you should learn this Excel skill:

1. Reversing the order of a comma-separated string in Excel allows you to reorganize your data in a more meaningful way.
2. It also allows you to examine the data from a different perspective, potentially uncovering new insights or patterns.
3. When presenting data, reversing the order of a comma-separated string makes your data easy to follow and digestible.
4. Often you must sort comma-separated data strings to categorize the given data. By flipping the comma-separated string you can achieve the desired sorting sequence.
5. It allows you to perform further operations or transformations on the data, such as splitting the string into separate cells or extracting specific elements.

Find below six effortless and smart ways to reverse the order of a comma-separated value string in Excel:

Use Flash Fill to Reverse the Order of a CSV String

Flash Fill is an intelligent module of the Excel desktop and web app. This tool scans the pattern of data entry made by you and uses that pattern to fill columns or rows with relevant data.

You need to show the pattern only once. Then, use a dedicated shortcut or ribbon button to use Flash Fill. Now, follow the steps as outlined below:

1. Identify the first cell of the CSV string dataset.
2. Now, reverse the order manually by typing the comma-separated strings in the adjacent blank cell (B2) to the right side.

3. Click the Data tab on the Excel ribbon.
4. Go to the Data Tools block.
5. While highlighting cell B2 (manually reversed CSV string cell), click the Flash Fill button.
6. Excell will automatically reverse the rest of the rows of CSV strings.

7. Alternatively, you can hit Ctrl + E to use the Flash Fill module quickly.

This method is only suitable when you believe that the input datasets are consistent and organized.

Flash Fill is only available in Excel 2013 and later editions. So, you may not find this method useful in all Excel desktop or web apps.

Reverse Order of a CSV String Using Text to Column and CONCAT

In this method, you’ll first split the CSV into individual items into separate columns. Then, use a function to rejoin these separated items with commas but in reverse order. Here’s how you should start:

Split CSV String Using Text to Column

1. Highlight the input data.
2. Click the Data tab on the ribbon menu.
3. Hit the Text to Columns button inside the Data Tools commands block.

4. The Text to Column wizard will show up.
5. There, click the Delimited option and click Next.

6. Choose Comma as the Delimiters.

7. On the next screen, click Finish.

You should now see splitter strings in five different columns. The columns will increase or decrease according to the input data.

Rejoin Splitted Items Using CONCAT

1. Go to the cell where you want to re-organize CSV strings.
2. Copy and paste the following formula inside the selected cell:

=CONCAT(E2,",",D2,",",C2,",",B2)

3. Hit the Enter key.
4. Use the fill handle to copy the formula across the column.

Copy the output data as values in another column if you need text strings. Also, you can hide the split columns to declutter the worksheet.

You must modify the above formula according to your worksheet if the split item columns aren’t as shown in the above image.

Use a Custom Function to Reverse the Order of a CSV String

You can create a custom function to reverse the order of CSV strings on Excel. To make this custom function, you need to use a VBA script. Find below the steps you can follow:

1. Open Excel VBA Editor by pressing Alt + F11 together.
2. Now, click Insert and then click Module.
3. Copy this script into the module:

Function ReverseString(str As String, Optional delimiter As String = ",") As String
    Dim arr() As String
    Dim i As Long
    
    arr = Split(str, delimiter)
    
    For i = UBound(arr) To LBound(arr) Step -1
        ReverseString = ReverseString & arr(i) & delimiter
    Next i
    
    ReverseString = Left(ReverseString, Len(ReverseString) - Len(delimiter))
End Function

4. Click the Save button and close the VBA Editor tool.

Now, you can use the above VBA code just like any other functions you use on Excel like SUM, AVERAGE, and so on. Here’s how:

1. Highlight the cell (B2) where you want the reversed CSV string.
2. Type the = sign.
3. Now type ReverseString as a function.
4. Enter a starting parenthesis and then select the cell reference (A2) where you got a CSV string.
5. Close the formula with a closing parenthesis.

6. Hit Enter to reverse the order of a comma-separated string in the selected cell.

7. Drag the fill handle down the column to apply this to other CSV string data.

Reverse the Order of a CSV String Using Office Scripts

Microsoft introduced a novel scripting language for Excel, named Office Scripts. It works on both the web and desktop versions of Excel.

However, the feature is only available to the Business Standard subscribers of Microsoft 365.

If you’re using an Excel desktop or web app provided by the workplace or institution, then you might already have the Office Scripts functionality.

Look for the Automate tab on the Excel edition you’re using. If you got the Automate tab, get started with these steps:

1. Click the Automate tab and choose the New Script option inside the Scripting Tools block.
2. The Code Editor panel will appear on the right side of Excel.
3. Press Ctrl + A inside the panel and press Delete to erase the existing code blocks.

4. Now, copy and paste the following Office Scripts code inside the Code Editor:

function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	// Set range B2 on selectedSheet
	selectedSheet.getRange("B2").setFormulaLocal("=textsplit(A2,\",\")");
	// Paste to range B3:B6 on selectedSheet from range B2 on selectedSheet
	selectedSheet.getRange("B3:B6").copyFrom(selectedSheet.getRange("B2"), ExcelScript.RangeCopyType.all, false, false);
	// Set range G2 on selectedSheet
	selectedSheet.getRange("G2").setFormulaLocal("=concat(F2,\",\",E2,\",\",D2,,\",\",C2,\",\",B2)");
	// Paste to range G3:G6 on selectedSheet from range G2 on selectedSheet
	selectedSheet.getRange("G3:G6").copyFrom(selectedSheet.getRange("G2"), ExcelScript.RangeCopyType.all, false, false);
	// Set visibility of column(s) at range B:F on selectedSheet to true
	selectedSheet.getRange("B:F").setColumnHidden(true);
}

5. Click the Save script button on the Code Editor toolbar.
6. Click the Run button to execute the code.

The above code will automatically split the content of cell range A2:A6 in cell range B2:F6. Then, it’ll use the CONCAT function to re-organize the individual text strings into a CSV string in reverse order.

In the current example, the code works with input values in the cell range A2:A6. It generates the outputs in G2:G6.

Not to mention, the script automatically hides columns B, C, D, E, and F so that your worksheet looks clean and organized.

You can modify the cell and cell range references as mentioned below according to your own dataset to make the work also work for your Excel worksheet:

• B2 has the TEXTSPLIT function so change it accordingly.
• Similarly, change A2 to the first cell where you got the CSV string.
• Modify B3:B6 according to the vertical height of the input CSV strings in your dataset.
• G2 is the first cell for output so change it if needed.
• Also, change G3:G6 according to the vertical height of CSV strings in the input column.
• Change B:F to the column range of actual columns you want to hide.

Flip the Order of a CSV String Using The Excel VBA

A better way to automate the CSV string reversing process in Excel is Excel VBA.

Find below a simple script that lets you flip the order of the comma-separated strings in any cell and for any number of rows.

Also, the code is powerful enough to reverse the order of CSV strings of unlimited items. For example, the current formula has five comma-separated items.

But, your dataset could contain hundreds and more comma-separated items and the script would still work.

Here is the VBA script you should use along with the steps to implement it:

1. Bring up the Excel VBA Editor by pressing Alt + F11 keys.
2. Click the Insert button on the toolbar and choose Module.
3. Into the new blank module, copy and paste the following VBA script:

Sub ReverseCSVStrings()
    Dim inputRange As Range
    Dim outputRange As Range
    Dim inputCell As Range
    Dim outputCell As Range
    Dim originalString As String
    Dim reversedString As String
    Dim stringArray() As String
    Dim i As Long

    Set inputRange = Range("A2:A6") ' Change the input range as needed
    Set outputRange = Range("B2:B6") ' Change the output range as needed

    For Each inputCell In inputRange
        originalString = inputCell.Value
        stringArray = Split(originalString, ",")
        ReDim Preserve stringArray(LBound(stringArray) To UBound(stringArray))

        For i = LBound(stringArray) To UBound(stringArray) / 2
            Dim temp As String
            temp = stringArray(i)
            stringArray(i) = stringArray(UBound(stringArray) - i)
            stringArray(UBound(stringArray) - i) = temp
        Next i

        reversedString = Join(stringArray, ",")

        Set outputCell = outputRange.Cells(inputCell.Row - inputRange.Row + 1)
        outputCell.Value = reversedString
    Next inputCell
End Sub

4. Don’t forget to change the input data cell range which is Range("A2:A6") to a different cell range according to your worksheet.
5. Similarly, change the output data cell range, Range("B2:B6") according to your dataset.
6. Click the Save button.
7. Close the VBA Editor.

8. Press Alt + F8 to call the Macro dialog box.
9. Select the ReverseCSVStrings macro.
10. Hit Run to execute the code.

Excel VBA will re-order your CSV string inputs for thousands of rows instantly.

Note: Excel VBA-based activities on Excel are irreversible. So, create a backup copy of your worksheet before proceeding with this method.

Reversing the Order of a CSV String Using Power Query

Suppose, you’re importing CSV string data from an external server but the order of the items isn’t the way you want. You find that reversing the CSV string order would work.

So, you must use Power Query to re-organize such CSV strings when importing the data. This way, you can get CSV strings the way you want.

Also, you can use Power Query to reverse the order of comma-separated strings of existing worksheet data as well. Find below the quick steps to follow:

1. Select the CSV string data needs rearranging.
2. Click Data on the Excel ribbon menu and choose From Table & Range.
3. Click OK on the Create Table dialog box.

4. You should now see your CSV string data in a column on Power Query.
5. Click the column heading.
6. Hit Split Column drop-down button on the Power Query ribbon menu.
7. On the context menu that shows up, click By Delimiter.

8. On the Split Column by Delimiter dialog box, click OK.

9. You should see different columns for different strings on the Power Query Editor as the image shown below:

10. Since you need to reverse the order of the CSV strings, start selecting all the columns on Power Query from right to left.
11. Then, right-click on the rightmost column and click Merge Columns.

12. On the Merge Column wizard, choose Comma as the Separator, and click OK.

13. Power Query will instantly re-organize the CSV strings.
14. Click File and choose Close & Load To.

15. On the Import Data dialog box, click Existing worksheet.
16. Now, highlight the cell range where you want the output CSV strings.
17. Click OK to add the values.

So, now you got reversed comma-separated string values in Excel from Power Query.

Conclusion

Until now, you learned six methods to reverse the arrangement of CSV string in Excel. Of all these methods, Flash Fill is the recommended one if your worksheet or dataset isn’t large.

If you got a large dataset and don’t want to do manual work to show Excel a pattern, you can use the methods based on the Text to Column and CONCAT (CONCATENATE) functions. It’s available in most desktop apps of Excel including the web app.

Use Excel VBA, Office Scripts, and Power Query if you’re an expert Excel user and don’t mind handling a few lines of code.

These methods let you automate the process and re-organize thousands of rows of CSV strings in seconds.