Split a single cell into multiple rows

[sharshra]

I have a cell with many items listed one below the other. I would like to pull the cell content into separate rows so that it can be managed better. Can someone advise how to do this?

See the example below. Cell A1 has 4 items (refer 'single cell.png'). I want to pull each of these into 4 rows (refer 'cell content in separate rows.png').

I tried with Text to Columns option, but it is not giving the expected result. Tried to search in the internet, but couldn't find an appropriate solution.

 

[Anonymous1378]

Try =TEXTSPLIT(A1,,CHAR(10))?

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1")
in
    #"Split Column by Delimiter"

Book2
	A	B
1	Column1	Column1
2	abc def ghi jkl	abc
3		def
4		ghi
5		jkl
Sheet1

 

[sharshra]

Thanks, Anonymous. It worked I just added Transpose to get these into rows.

=TRANSPOSE(TEXTSPLIT(A1,CHAR(10)))

Thanks alansidman for your suggestion.

 

[Anonymous1378]

The consecutive commas in the function was not a typographical error; it should eliminate the need for TRANSPOSE()

 

[Peter_SSs]

Edit: Oops, simultaneous posting

I just added Transpose to get these into rows.

You shouldn't need to. Did you omit the double comma in the earlier suggestion?

 

[sharshra]

Good point. I did omit double commas before Now I added double commas in TestSplit & removed Transpose. Thanks for noticing

 

[sharshra]

I just need a small change. The source cell has special character (bullet). When it is split, I want to remove the special character & have only the text in separate rows. I would have done this using LEFT formula but not sure how to combine this with TEXTSPLIT.

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Anonymous1378]

I don't know what unicode character this bullet refers to, but nesting a SUBSTITUTE() inside of the TEXTSPLIT() to get rid of it would probably suffice?