MS Excel Convert column data into row

Question

I have all data in a column in MS excel.
Please see the attached picture: .

I am trying to covert data with its corresponding heading and other data associated with its "Acct No.". as sample showing in the picture:

I want to separate Blue highlighted cell data into 2 separate column as it contains property Desc and address as shown into I have trying Text to column and also Textsplit() but non of them work. could someone please help me out with this? Thank you.

Answer 1

You may try following method:

Use "Text to Columns" feature to split the text via colon (:). You will get the columns as image below.

Using =IF(ISERROR(FIND("Opening Bid",B1)),B1,TEXTBEFORE(B1,"Opening Bid")) to get the data as following image.

Make the titles that you desired.

Then please enter the formula =FILTER($D$1:$D$12,$A$1:$A$12=H$1), and pull right to "Name" column.

For "Desc" and "Address" column, you may use the formula =TEXTBEFORE(FILTER(D1:D12,A1:A12="Desc")," ") and =TEXTAFTER(FILTER(D1:D12,A1:A12="Desc")," ").

For the "Opening Bid" column, the formula is =FILTER($C$1:$C$12,$C$1:$C$12<>"").

Answer 2

MS Excel is a powerful tool for data analysis and manipulation. One common task users face is converting column data into rows. This might sound simple, but choosing the right method can save you a lot of time and hassle.

To convert column data into a row in MS Excel, follow these steps:

1. Select the column data you want to convert.
2. Right-click and choose "Copy."
3. Click on the cell where you want the row to start.
4. Right-click and select "Paste Special."
5. In the "Paste Special" dialog box, check the "Transpose" option.
6. Click "OK."

This will paste the column data into a row. Transposing switches the orientation of the data, converting columns to rows and vice versa. This method is useful for reformatting data quickly.