Forum Discussion

zau73's avatar
zau73
Copper Contributor
Jul 15, 2024

How to re arrange data in excel?

Hi, Currently i have my parameters all in the same column. But i need to arrange them in rows and column. For example: I want to have the Name and Count as column, that means i only want to 2 ...
Excel
Formulas and Functions
  • Harun24HR's avatar
    Harun24HR
    Jul 15, 2024

    zau73 You can use WRAPROWS() function

     

    =DROP(WRAPROWS(A1:A14,3,0),,-1)

     

     

    If you want to clean Name: and Count: strings then use SUBSTITUTE() function.

     

    =SUBSTITUTE(DROP(WRAPROWS(A1:A14,3,0),,-1),{"Name: ","Count: "},"")

     

     

djclements's avatar
djclements
Bronze Contributor
Jul 15, 2024

zau73 If you are using Excel for MS365 or Excel for the web, you could try the following:

 

=WRAPROWS(TEXTAFTER(TOCOL(B:B, 1), ": "), 2)

 

Adjust the column reference as needed (I assumed column B contains the data in this example).

 

TOCOL removes the blank rows by setting the optional [ignore] argument to 1. TEXTAFTER returns only the values after the colons (": "), and WRAPROWS reorganizes the data into 2 columns.

 

Also, the numeric values will initially be returned as text strings. To convert them to numeric values, use the following:

 

=LET(arr, WRAPROWS(TEXTAFTER(TOCOL(B:B, 1), ": "), 2), IFERROR(--arr, arr))

 

See attached...

Resources