Converting a string of delimited text options into a list of text options within a cell.

Hi

I have exported some questionnaire data into a spreadsheet. The yes/no straightforward answers are no problem, but I am having an issue with the multiple-choice answers as they have been exported in one cell delimited by semi-colons and hashes, eg

Option One;#Option Two;#Option Three;#Option Four

Because each cell relates to one questionairre, I am looking to create a list instead of the string in the one cell, eg:

Option One
Option Two
Option Three
Option Four

I know how to do this manually but I have over 450 cells to do with up to 15 options in each, and really want to find a faster way.

Can anyone help, and also are you able to put it in simple language? Something tells me it's a really simple process but currently it is passing me by, and I get easily confused when initally dealing with new jargon

Thanks


Pebbs

Highlight your range. Go to Data>Text-To-Columns. Choose Delimited and use a semicolon as your delimiter.

That's perfect, thank you so much

---------- Post added at 11:30 AM ---------- Previous post was at 11:26 AM ----------

I just tried it again - this option would be great but pops everything into different columns, overwriting what was already there. I need it all in the same cell

So you want it on four separate lines within the same cell?

Thanks Richard
Your solution has the same problem as Ben, in that it takes the data out of the original cell and would require copy and pasting to get it all back into the one cell again. I would have a ridiculous amount of data to do that with and don't have time. I saw someone do it once so know it's possile... somehow!
Pebbs

---------- Post added at 11:34 AM ---------- Previous post was at 11:34 AM ----------

Originally Posted by BenMiller

So you want it on four separate lines within the same cell?

Yes please!

I'm sorry. That would require a macro, and I'm not much of a programmer.

That's cool, thanks for helping Ben

Is there a way to avoid macros, anyone?

Not to my knowledge. Is there a reason you want them all on the same cell? It is never recommended to put two pieces of data in the same cell.

I have a piece of code to seperate everything else in a different section of the spreadsheet, which I can probably use as is but knowing the way my boss thinks he wil want to see everything at a glance. Currently the date will not display because the width of the cell is too small for the string.
The annoying thing is that I have seen it done before so know it's possible, but am missing a step in the process. I will keep trying!

Hi Richard
This is where I prove how non-Excel-literate I am: when entering the code, what do I need to add to it in order for it to recognise the instruction? Eg when doing an addition between cells you first type in '=SUM...'

Thanks

Start it with an equals sign =

Richard, brilliant - I did not think of using CHAR(10) and pasting values ... that's great

Technically, you can just use

=SUBSTITUTE(A1,";#",CHAR(10)) and copy/paste values - without having to first split the columns.

Thanks Guys

For some reason, it was nearly there - the values just had an odd hollow box symbol next to them, but when I copied and pasted as just a value it worked.

You are both just made my day go from being pretty rough to having something opretty decent and satisfying to smile about, thank you so very much

Thanks again

Nikki, AKA Pebbs

Originally Posted by Richard Buttrey

@Ben
Good catch. I didn't spot that one!

To make it accurate, the OP mentioned that some cells had just one value. In that case, the new column should have:

=IF(ISNUMBER(FIND(";#",A1)),SUBSTITUTE(A1,";#",CHAR(10),A1)

or

=CHOOSE((ISNUMBER(FIND(";#",A1)))+1,A1,SUBSTITUTE(A1,";#",CHAR(10))

Hi

@Richard - I had wondered if it was concatenating but then I assumed that was just if they were side by side.

Just out of curiosity seeing as you're on a roll, I am having to click in each field and press return in order to get the list to display. Is there any reason for this or any way around it? If not, don't worry - you've already saved me hours worth of work.

Thanks

For some reason the IF statement does not work on my system - I think it is looping or something like that.

The CHOOSE option works fine and as you say only picks out the data that is there meaning I can have individual records.

What does OP mean by the way?

Cheers

Originally Posted by Pebbs1981

For some reason the IF statement does not work on my system - I think it is looping or something like that.

The CHOOSE option works fine and as you say only picks out the data that is there meaning I can have individual records.

What does OP mean by the way?

Cheers

Sorry, I left out a closing parentheses. Should be:

=IF(ISNUMBER(FIND(";#",A1)),SUBSTITUTE(A1,";#",CHAR(10)),A1)

OP means Original Post(er)

Originally Posted by BenMiller

As Richard pointed out before, highlight all the cells (after you pasted values), right-click>format cells>tab over to Alignment>Check "Wrap Text".

Ah sorry, I missed that. - was too busy trying to figure out the code.

Thank you so much

No problem ... the real genius was Richard; I did not think of inserting the line break as a character...