vlookup and split text data from one cell across different cells

I have an excel spreadsheet that contains a lot of different text data in one cell. (these are answers to different survey questions) Now I would like to split up the answers among different cells, so that I can proceed with the analysis. I know there is a way to do this, but unfortunately my VBA skills are not sufficient.

More concretely I would like to have:
1) All the data that is separated by hyphon in a cell of its own.
2) The selection of pogram features all listed in a single cells (2nd question)
3) Ideally have the questions themselves not repeated in the cells but just the answers.

I will have to repeat that for a set of data of 250 rows or so.

Can anybody help me out with that? I would be very grateful! Thanks a lot.


Current the data is presented like this in one cell:

-Are you interested in being considered Programs in other locations and dates : Yes, please consider me for the SIM 2015
- Which are the most important Social Innovation Management program elements and benefits to you? You can choose up to 5 progam features. : Building a global network of likeminded individuals during and after the program Gaining hands-on experience at your local apprenticeship organization Developing skills and knowledge to drive social change Gaining international experience and having the opportunity for cultural immersion Being able to change perspective and learn a different way of thinking
- How many years of relevant work experience do you have (including internships) : 3-5 years
- How did you first learn about this program? : Development Sector Platforms (PCDN, Africa Desk, Escape the city, etc.
- Please tell us about your interest and/or experience in any two of your chosen areas. (max 150 words) : I chose Education and Environment/Sustainability as they go hand-in-hand. W
- 3. What are your TWO primary areas of interest when it comes to creating social change : Education Environment/Sustainability
- 2. Why is social change or social innovation important to you? (max 200 words) : The sense for a community is being lost and with it knowledge is being lost. A
- 1. Tell us your story. Please note that any of the latter options should not exceed 2 mins limit : I was born in a small town....
- What is your skype username? : 123tzub
- Which languages do you speak? Please indicate also your fluency level in terms of native, fluent, very good, good and basic.
: German - native English - fluent
- What city do you live in? : Apple
- What is your country of residence? : XXX
- What is your nationality? : Finnish
- What's your age? : 33
- What is your gender? : Female

Thanks for the help thus far! Any ideas on formulas which will help me to seperate the data into different cells in the first place? The text to column formula doesn't work for me, what am I doing wrong?

Originally Posted by sourabhg98

Hi
What do you mean by in the first place?

All the above mentioned data is currently in ONE cell. More concretely I would like to have:
1) All the data that is separated by hyphon in a cell of its own. (these are 16 different questions, so I would like to have the answer in 16 cells). Then I can proceed with the "clean up" formula that you were so kind to suggest.

2) Also the 2nd question tends to have up to 5 diffferent answers. I would like to have them displayed in their own cells. However they don't have a delimiter thought.

Any ideas on how to do that? Thanks so much!

First of all thanks so much.

I tried running it and as long as the answers are in single rows, it works beautfifully. However, since my data is all cramped into a single call (A1), it doesn't quite work as it recreates all the content 14 times. How can I automate the process of having all answers in single rows?

Once again thanks in advance for your help!

I have one more question: can the macro be progammed as such that all the answers displayed horizontally (in rows) rather than in columns as it is right now?