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
    convert column into row using word, convert column into cell 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 |
      convert column into row using word, convert column into cell using word
    5. Select "Replace all"

  • Solve Using Excel
    convert column to cell in excel, convert column to single cell in excel, convert column to single cell in excel using formula

    • 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:

Related Article:

Post a Comment

Thank you for comment, I really appreciate your view.

–>