Convert Column into Row | Tips & Tricks

Converting Column into Row or Cell

Converting column into row is useful many times while working with Database, CSV file or in Microsoft Dynamics NAV. We have columns of data in Excel sheet and we just want to convert a specific column into cell.

In case of Microsoft Dynamics NAV we copy rows of object from certain filtering condition and we paste those rows into excel. After that we need to make filter on specified objects, for this purpose we need only objects number separated with bar (|).

While working for CSV file some time we need to take a row from a column and convert them into comma-separated list in a cell so user can copy that cell and use it.

To solve this problem, I found this three solutions very helpful. For me using Microsoft Word was the best option but now I love to use formula in Office 365 Excel.
Listed are different solutions

1. Solve Using Word
2. Solve Using Excel

Suppose column to convert is Column A in excel.

• Solve Using Word
  

1. Copy the column in Excel (Copy Column A)
2. Open Word and "Paste special" as Text Only (T)
3. Select the data in Word (the one that you need to convert to text separated with ,), press Ctrl-H (Find & replace)
4. In "Find what" box type ^p and in "Replace with" box type |
   
5. Select "Replace all"

• Solve Using Excel
  

• MANUAL
  Our data is in Column A, now put the following formula in column B:
  B1: =A1
  B2: =B1&"|"&A2
  After this paste column B2 down the whole column. The last cell in column B should now be a comma separated list of column A.
  (Note: This method is best fro limited number of rows, be careful)

• USING FORMULA

  Use TEXTJOIN formula as
  =TEXTJOIN("|",TRUE,A:A)
  This formula only works on Office 365 Excel.

Converting column of data from excel into single cell is quite handy.

Hope you like this tips & trick. If you have different way of doing this please share in comment. Thanks.

Reference:

Convert column to comma separated list

Related Article:

Find duplicate Rows in Excel