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.

 

[alansidman]

Here is a table of ASCI codes

ASCII Table - ASCII Character Codes, HTML, Octal, Hex, Decimal

Ascii character table - What is ascii - Complete tables including hex, octal, html, decimal conversions

www.asciitable.com

 

[sharshra]

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?

Can you please try with any character not necessarily bullet & advise how to get rid of that character after TEXTSPLIT? I'm trying, but it's not working somehow.

 

[Fluff]

Please see post#9 an image is of no real use.

 

[sharshra]

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.

Sample data is here

split text.xlsx
	A
1	• afd • bl • crerer • diu
Sheet1

The following formula split the text & put them into separate rows. The output includes bullet also.

=TEXTSPLIT(A1,,CHAR(10))

But, I need the output from the formula without bullets as shown below.

split text.xlsx
	A
3	afd
4	bl
5	crerer
6	diu
Sheet1

 

[Anonymous1378]

Try =TRIM(SUBSTITUTE(TEXTSPLIT(A1,,CHAR(10)),CHAR(149),"")) ?

 

[Peter_SSs]

Can you please try with any character not necessarily bullet & advise how to get rid of that character after TEXTSPLIT?

Try

=TRIM(TEXTSPLIT(MID(A1,2,LEN(A1)),,CHAR(10)&LEFT(A1,1)))

 

[sharshra]

Try =TRIM(SUBSTITUTE(TEXTSPLIT(A1,,CHAR(10)),CHAR(149),"")) ?

This worked perfectly. Thanks Anonymous1378