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
Report-2018-01-25-14_15_20_ver3.xls
Mattia MinerviniAsked:
Who is Participating?
 
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.
FixReportV1.xlsm
0
 
Rob HensonFinance AnalystCommented:
No attachment
0
 
Mattia MinerviniAuthor Commented:
Sorry, try now.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rob HensonFinance AnalystCommented:
yep, there now.
0
 
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.
0
 
Mattia MinerviniAuthor Commented:
yes, it's work.but i need a formula to apply on entire sheet (you see only an example...)
Thanks
0
 
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?
0
 
Mattia MinerviniAuthor Commented:
max 6 or 8 rows
0
 
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
Report-2018-01-25-14_15_20_ver4.xlsm
0
 
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
Report-2018-01-25-14_15_20.xls
0
 
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!
0
 
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.
Or
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!
/Zeth
Report-2018-01-25-14_15_20_ver5.xlsm
0
 
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
0
 
Mattia MinerviniAuthor Commented:
it works, at once.
0
 
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
thanks
Report-2018-02-06-11_27_10.xls
0
 
Zeth LarssonCommented:
Very very strange error:-)
I'l se what it can be!
0
 
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.
FixReportV2.xlsm
0
 
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:-)
FixReportV3.xlsm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.