Handling Data Multiple values in single cell in one column

Question

Hello Good Evening All,

I have a question in excel when I worked on it several hours but not final answer, achieved only little, realized I need someone help, please help me here with this issue

I have excel sheet (attached data sample), each employee has one or none or more than one technology column, I need to convert (split) that each technology value with other column values for that row please see below.,

for easy to understand I applied colors at bottom section of the image, so please help with this data breakup (technology column values split)

What I DID so far

select the column, and then Data --> text to columns then delimiter, Other , Alt+0010 it separated all of the values as separate columns then I stuck here

please kindly help me

Thank you in advance

MIlan

Answer 1

Hi,

Here's what worked for me

1. Select A2:E8, copy it and paste it in MS Word
2. Copy the data from MS Word and paste it back in the Excel workbook

The steps above will surprisingly resolve the multiple entries per cell issue.  You will now see each entry appearing in its own cell

3. Select data in all columns except the technology column and click on the Merge and Centre icon to unmerge the cells
4. Select data in all columns except the technology column and press Ctrl+G > Special > Blanks > OK
5. Press =, then up arrow and finally Ctrl+Enter
6. Select data in all columns except the technology column and press Ctrl+C.  Right click > Paste Special > Values > OK

Hope this helps.

Answer 2

AshishNo words to say to you,

I am very lucky, its worked like a charm,

I am complete with this , Thank you a ton to you, God bless you all, tried to help me

Grateful to you

Answer 3

You are welcome.