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
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
Rob Henson
No attachment
ASKER
Sorry, try now.
yep, there now.
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.
The carriage return then gets recognised as new row.
ASKER
yes, it's work.but i need a formula to apply on entire sheet (you see only an example...)
Thanks
Thanks
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?
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?
ASKER
max 6 or 8 rows
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
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
ASKER
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
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
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!
You need some function that correct what is wrong! Is that right?
Sorry I missunderstand you. My english is not the best!
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
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
ASKER
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
Now i need to take macro and apply it on sheets (and uity cells of sheet) we need, how may i do?Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it works, at once.
ASKER
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
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
Very very strange error:-)
I'l se what it can be!
I'l se what it can be!
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
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
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
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