How to convert comma separated text string to a list in Excel?
Converting a comma-separated text string into a list or rows in Excel can be a common task, particularly when dealing with data importation from CSV files or other text sources. Below is a detailed guide that walks you through various methods to achieve this, utilizing Excel’s built-in features as well as formulas.
Convert comma separated text string to a list in Excel
Convert comma separated text string to list with a formula (Excel 365)
For users with Excel 365, the TEXTSPLIT function is an efficient and straightforward way to convert comma-separated strings into rows or columns. This function automatically adjusts cell ranges (spills results) based on the output size.
Click on the cell where you want the result to start, enter the below formula, and press Enter key. The cells containing the comma separated text strings are converted to a list at once. See screenshot:
=TRANSPOSE(TEXTSPLIT(TEXTJOIN(", ", TRUE, A1:A4), ", "))
- TEXTJOIN(", ", TRUE, A1:A4):
This part joins the text values from cells A1 to A4 into a single string, separated by a comma and a space (, ).
The TRUE argument ensures that empty cells in the range are ignored. - TEXTSPLIT(..., ", "): The combined string from TEXTJOIN is then split back into individual items using the comma and space (, ) as the delimiter.
This creates an array of the original values. - TRANSPOSE(...): Finally, the TRANSPOSE function flips the array from a vertical column into a horizontal row (or vice versa, depending on the original orientation).
- Advantages:
- Ease of Use: Simple and straightforward, requiring no additional tools.
- Dynamic Arrays: Automatically adjusts if data in the source cell changes, ensuring your output is always up-to-date.
- Disadvantages:
- Limited Availability: Only available in Excel 365, not accessible to users with older versions of Excel
Convert comma separated text string to list with Kutools for Excel
If you don't have access to Excel 365, Kutools for Excel provides a powerful and intuitive alternative that simplifies this task. With its "Split Data to Rows" feature, you can seamlessly convert comma-separated strings into multiple rows, making data management more efficient and straightforward.
After installing Kutools for Excel, please do as this:
- Click "Kutools" > "Merge & Split" > "Split Data to Rows", see screenshot:
- In the "Split Data to Rows" dialog box, specify the following operations:
- Select the cells that you want to split;
- In the "Delimiter" section, choose "Other", then type your delimiter (e.g., a comma and a space) to define how the cell content should be split;
- Finally, click OK button.
Result: The selected cell contents are split into a list by the commas.
- Advantages:
- Versatility: Offers more customization options than Excel's native functions.
- User-friendly: Provides a GUI that guides users through the process, making it accessible to those less familiar with Excel functions.
- Compatible with All Excel Versions: Kutools can be used in all Excel versions.
- Disadvantage:
- Need to install.
Convert comma separated text string to list with VBA code
The following VBA code may help you to convert multiple comma separated cell values to a column, please do as follows:
1. Hold down the "Alt + F11" keys in Excel, and it opens the "Microsoft Visual Basic for Applications" window.
2. Click "Insert" > "Module", and paste the following macro in the Module Window.
VBA code: Convert comma separated text string to rows or list
Sub RedistributeCommaDelimitedData()
'Updateby Extendoffice
Dim xArr() As String
Dim xAddress As String
Dim Rg As Range
Dim Rg1 As Range
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set Rg = Application.InputBox("please select the data range:", "Kutools for Excel", xAddress, , , , , 8)
If Rg Is Nothing Then Exit Sub
Set Rg = Application.Intersect(Rg, Rg.Parent.UsedRange)
If Rg Is Nothing Then Exit Sub
Set Rg1 = Application.InputBox("please select output cell:", "Kutools for Excel", , , , , , 8)
If Rg1 Is Nothing Then Exit Sub
xArr = Split(Join(Application.Transpose(Rg.Value), ","), ",")
Rg1.Resize(UBound(xArr) + 1) = Application.Transpose(xArr)
Rg1.Parent.Activate
Rg1.Resize(UBound(xArr) + 1).Select
End Sub
3. Then press F5 key to run this code, in the popped out dialog box, select the data range that you want to convert, see screenshot:
4. And click OK, in the following dialog, specify a cell where you want to output the result, see screenshot:
5. Click OK button, the selected comma separated cell values have been converted into a list of rows. See screenshot:
- Advantages:
- Compatible with All Excel Versions: VBA can be used in Excel 2007 and later versions.
- Automated Processing: Capable of handling large volumes of data in batches, making it ideal for repetitive tasks.
- Disadvantage:
- Requires Programming Knowledge: VBA code requires some programming expertise, which may not be user-friendly for beginners.
- Security Concerns: Enabling macros may pose security risks, especially when dealing with files from unknown sources.
Whether you prefer a no-code solution like Kutools, the simplicity of built-in Excel 365 formulas, or the robustness of VBA scripting, Excel offers various tools to efficiently convert comma-separated text strings into usable data formats. These methods enhance productivity and allow for more effective data management and analysis in your spreadsheets. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.
Related articles:
How to split cells into multiple columns or rows by carriage return?
How to split cell values into multiple columns 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