excel - separate all text from one row into multiple rows

Question

Hi, I have over 700, 5-letter words in row1. How do I separate this long string of words to one word per row in columnA? row1 ABIDE, row2 ABODE, row3 ABORT...etc. Thanks in advance!

Answer 1

Hi Nklrcade!

I'm Jen, and I'd be happy to help you out with this issue.

You can split the cell text first into multiple columns using Text to Column. Then, select these cells and copy them. Select a blank cell and right click to select 'Transpose'. The data will then be converted into multiple rows.

https://www.extendoffice.com/documents/excel/15...

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

I hope this information helps. If you have any questions, please let us know and we'll be glad to assist you further.

Best Regards!
Jen :)

Answer 2

Re: words in row to a single column

The syntax of the AnyPart function is...

=ANYPART(vText, vPiece, vDivider1, vDivider2)

Omitting vDivider2 causes it to use vDivider1.

The two dividers can be different values.

The function is part of the free "Custom_Functions" Excel Add-in.

It has 20+ custom functions that work exactly like the built-in Excel functions.

Download from OneDrive... https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU There is also an 'examples' Word.docx file that can be downloaded.

'---
Nothing Left to Lose

Answer 3

Hi,

Please check whether the following solution is helpful:

Step 1) Click on the cell that contains the words.

Step 2) Go to Data tab > click on Text to Columns.

click on Text to Columns dialog box will open.

Step 3) In the 1st page of the Text to Columns dialog box > select Delimited > click on Next.

Step 4) In the 2nd page > select Space > click on Next.

Step 5) In the 3rd page > click on Finish.

Result: the words will be distributed in columns.

Step 6) Select all the words > press Ctrl + C to copy > click on cell A2 > do a right click and click on Paste Special > in Paste Special dialog box, click on Transpose > click on OK.

Result

Next, You may delete the words in row#1 and move the Transposed list to cell A1.

Please respond if You require further assistance. I will try My best to be of help.

If I was able to help You, please mark My response as answer and helpful.

Thank You!

Answer 4

Hi,

If your version of MS Excel supports the Query Editor, then you may use the feature of Split data > By rows.