Extract hours row from a single unity excel cell (with multiple rows separated with carriage return)

Hi, i have an excel file created by an application.
This file is about time work of our employees, reporting start work time and end work time
For one employee, if he has one start time and one end time, all is ok.
but if in the same day he has two (or more) start time and two (or more) end time, this is the problem

two start time are united in the same cell, and two end time are united in another cell
i need every time in single cell

please see my attachment, maybe is more clear to understand
Mattia MinerviniAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
No attachment
Mattia MinerviniAuthor Commented:
Sorry, try now.
Rob HensonFinance AnalystCommented:
yep, there now.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rob HensonFinance AnalystCommented:
One quick fix is to copy and paste into MS Word and then copy and paste back to Excel.

The carriage return then gets recognised as new row.
Mattia MinerviniAuthor Commented:
yes, it's work.but i need a formula to apply on entire sheet (you see only an example...)
Rob HensonFinance AnalystCommented:
Have you tried copying the whole report into Word and back?

I could give a formula that will split the times from left to right, ie separate columns but I think to split into separate rows would need a bit of VBA.

Your sample had one with 2 times and one with 3 times. What is the most number of times that will occur in one cell?
Mattia MinerviniAuthor Commented:
max 6 or 8 rows
Zeth LarssonCommented:
Try this file.
You must activate macro when you open the file, to be able to use the file as intended.
In column A, click on the text "Add row", then a formatted row will be added below the last row.
But you must start with a correct worksheet, the macro can't correct what is wrong.

If it is necessary, it is possible to write protect the hole worksheet, and then user must fill in data in a form that open on double click on "Add row".

Hope this will be to some help.

Regards /Zeth
Mattia MinerviniAuthor Commented:
sorry , i don't know how use it
my excel was an example, please check original excel in access (cell C61-C62)
this was generated by a  software, so i need some macro or workaround to solve
Zeth LarssonCommented:
Ok, My macro make it easy to do right from the beginning, if there is manual edit of the worksheet.
You need some function that correct what is wrong! Is that right?
Sorry I missunderstand you. My english is not the best!
Zeth LarssonCommented:
Ok Mattia
Here is a new try.
This macro will correct current sheet as your needs.
Double click inte in the merged cell in column C. Then the macro split the cell and write back values as you wanted it.

To use my file, move the report worksheet from your original file to my file, then you can use the macro.
Export the macro to a macro text file. And every time you need it in a new original report sheet, import that text file to your report file. It's very easy the third time.
But you must save your report file as a MS Excel macro activated file (*.xlsm).

Hope it work well for you to!
Mattia MinerviniAuthor Commented:
Hi, seems to be ok.
Now i need to take macro and apply it on sheets (and uity cells of sheet) we need, how may i do?Thanks
Zeth LarssonCommented:
God morning, at least here in Sweden.
The easiest way to use my macro, is to copy your original worksheet into my file, run the macro by double click, and then SAVE A COPY of the file as you need. My original file is then closed WITHOUT SAVE.
That procedure is may be the easiest to repet for each new report you get.

Procedure to copy worksheet from one workbook to another.
1: Open your original report and my attached file (FixReportV1.xlsm) side by side in MS Excel.
2: Right click on you original worksheet, and select "Move or copy..."
3: In the drop down "To book" select my file (FixReportV1.xlsm)
    In the "Before sheet", select "Empty"
    and check the "Create a copy"
3: Click OK.
Then in my file, you can run the macro by double click on a cell in column C with several values.

Some options in the code.
a: In the code you have the option to stop the macro if it isn't equal number of time values in columns C and D.
    This check is disabled now.
b: In the code you can select if you want to create frames on the unmerged cells. That is disabled now.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mattia MinerviniAuthor Commented:
it works, at once.
Mattia MinerviniAuthor Commented:
Hi Zeth, why macro works only on "orange" cell not in white cell?
please try to apply to this excel sheet, and see behaviour on row 65 and 66
Zeth LarssonCommented:
Very very strange error:-)
I'l se what it can be!
Zeth LarssonCommented:
Ok, it's done now.
It was you original template workbook, that has values only in colored cells, so I made a check that dbl click must be in a colored cell.
That check is removed now.
It had to do with which color the result vill have, but I don't think it will be any trouble with than anyway,
Hope this file will be better for you.
Zeth LarssonCommented:
Try this file, also.
It is coded with an application level object, that shall do the same job as previous version (2).
But now (if it works as intended), you open this file, and let it only be open in the background.
Then open your reportfiles and double click the merged cells as before. No more needs to export worksheets, I hope:-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.