WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to convert columns to text in Excel

January 25, 2024
31.2K Views

With the use of an adjustable delimiter, we can learn how to convert columns to text in excel. The Text to Columns button is located in the Data Tools group on the Data tab of the ribbon.In the displayed example, a vertical pipe (|) character is used to demarcate the content in column B. The pipe is the custom delimiter that has been selected in the Text to Columns dialogue box. You can validate results by looking at the preview section at the bottom of the dialogue. To complete the action, press the Finish button. You can also find the process of how to convert columns to text in excel below.

WPS Office: Use Word, Excel, and PPT for FREE, No Ads.

logo

How to convert columns to text in excel online, 2016, 2019 and other versions:

1.Use the Convert Text to Columns Wizard to divide the content of a single Excel cell into different columns. When you wish to divide a list of full names into first and last names, for instance.

2.Pick the group that includes the entire names.

3.Click Text to Columns under the Data Tools section of the Data tab.

4.The dialogue window shown below appears.Click Next after selecting Delimited.

5.Except for the Comma and Space check box, deselect all of the check boxes under Delimiters.Click on Finish.

6.Commas and spaces are used as delimiters in this example. Your data may contain more delimiters. Try different combinations of checking and unchecking the various check boxes. You are given a real-time preview of the data separation process.Result:

With The TEXTJOIN Function, Convert A Column List To A Comma Separated List in excel:

1.With a defined delimiter, the Excel TEXTJOIN function joins several data from a row, column, or range of cells.

2.Please choose a blank cell, such as cell C1, and type this formula =TEXTJOIN to transform a column list into a comma separated list (, ,TRUE,A1:A7) (The column you will convert to a comma-separated list is A1–A7; the separator ,  designates how you want the list to be organized.) See the illustration below:

How to convert column list to comma separated list using VBA in excel:

1.To open a WPS Visual Basic for Application window, use F11 on the keyboard while holding down the ALT button.Copy the VBA into the module by selecting Insert > Module.To launch VBA, click the Run button or press F5.You can choose the column list you want to convert from an on-screen dialogue. See illustration:

2.Once you clicked OK, a new dialogue box appeared asking you to choose a cell. See illustration:

3.When you click OK, a cell will now include a list of all the values from the column list, each separated by a comma.

Note: This above written article is an attempt to show you how to convert columns to text in excel online, 2016, 2019 and other  latest versions, in both windows and mac. You just need to have a little understanding of how and which way things work and you are good to go. With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.                                    

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.