· 1 months ago
Can someone help in converting a cell wish Value
Football, Baseball, Cricket
into
1. Football, 2. Baseball, 3. Cricket
Football, Baseball, Cricket
into
1. Football, 2. Baseball, 3. Cricket
How to convert column list to comma separated list in Excel?
If you want to convert a column list of data to a list separated by comma or other separators, and output the result into a cell as shown as below, you can get it done by CONCATENATE function or running a VBA in Excel.
Convert column list to comma separated list with CONCATENATE function
Convert column list to comma separated list with VBA
Convert column list to comma separated list with Kutools for Excel
Reverse concatenate and convert one cell (a comma serrated list) to a row/column list in Excel
Quickly convert a column list to a comma separated list with Kutools for Excel
Kutools for Excel’s Combine Columns or Rows utility can help Excel users easily combine multiple columns or rows into one columns/row without losing data. In addition, Excel users can wrap these combined text strings with carriage or hard return. Full Feature Free Trial 30-day!
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Convert column list to comma separated list with CONCATENATE function
In Excel, CONCATENATE function can convert the column list to a list in a cell separated by commas. Please do as follow:
1. Select a blank cell adjacent to the list's first data, for instance, the cell C1, and type this formula =CONCATENATE(TRANSPOSE(A1:A7)&",") (A1:A7 is the column you will convert to comma serrated list, "," indicates the separator you want to separate the list). See screenshot below:
2. Highlight the TRANSPOSE(A1:A7)&"," in the formula, and press the F9 key.
3. Remove curly braces {and } from the formula, and press the Enter key.
Now, you can see all values in the column list been converted a list in a cell and separated by comma. See screenshot above.
Convert column list to comma separated list with VBA
If the CONCATENATE function is a little tedious for you, you can use a VBA to quickly convert the column list to a list in a cell.
1. Hold ALT button and press F11 on the keyboard to open a Microsoft Visual Basic for Application window.
2. Click Insert > Module, and copy the VBA into the module.
VBA: Convert column list to comma separated list
Sub ChangeRange()
'Updateby20140310
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = rng.Value
Else
outStr = outStr & "," & rng.Value
End If
Next
OutRng.Value = outStr
End Sub
3. Click Run button or press F5 to run the VBA.
4. A dialog displayed on the screen, and you can select the column list you want to convert. See screenshot:
5. Click OK, then another dialog popped up for you to select a cell. See screenshot:
6. Click OK, and all values in the column list have been converted to a list separated by comma in a cell.
Tip: In the above VBA, "," indicates the separator you need, and you can change it as you need.
Convert column list to comma separated list with Kutools for Excel
You can also apply Kutools for Excel’s Combine utility to combine a column list, and separate each value with comma easily.
1. Select the column list that you will convert to a comma separated list, and click Kutools > Combine.
2. In the opening Combine Columns or Rows dialog box, you need to:
(1) Check the Combine rows option in the To combine selected cells according to following options section;
(2) In the Specify a separator section, check the Other separator option, and type comma , into following box;
3. Click the Ok button.
Now you will see all values in the specified column list are combined into one cell and converted to a comma separated list.
Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now
Demo: convert column list to comma separated list in Excel
Kutools for Excel includes more than 300 handy tools for Excel, free to try without limitation in 30 days. Download and Free Trial Now!
Normally Excel users can apply the Text to Columns feature to split one cell to multiple columns, but there is no directly method to convert one cell to multiple rows. However, Kutools for Excel’s Split Cells utility can help you easily get it done as below screenshot shown. Full Feature Free Trial 30-day!
Relative Articles:
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.