Hi all, i hope you can help me. I have a cell in Excel with data in a form which looks something like this....
I would like to split this data in to three columns for each date range in the string, for example:
I was hoping I could use the Date in the string as a delimiter, use the time in the string as a delimiter, then the remaining text until the next date, and was hopeful that Power Query might do it, and/or that AI could recognise the date and time formats and split on each occurrence, however nothing I have tried has worked so far (mind you, my PQ and AI knowledge is limited!).
I would need to do this for several thousand rows of data, and for the process to be repeatable for new data each month.
Does anyone have any ideas how I could achieve this please?
Thanks in advance for your help.
PS the response I would give would be tell those creating the data "don't collect data like this(!)" or at least that they should have some sort a delimiter in between the progress stages, but its an extract from a CRM system, and there is nothing we can do about it unfortunately.
| 16/09/2024 12:46 Preparing job 21/09/2024 15:55 Preparing for handover 22/09/2024 08:43 Handover complete 23/09/2024 09:43 Job in progress 25/09/2024 12:03 Job complete |
I would like to split this data in to three columns for each date range in the string, for example:
| 16/09/2024 | 12:46 | Preparing job | 21/09/2024 | 15:55 | Preparing for handover | 22/09/2024 | 08:43 | Handover complete | 23/09/2024 | 09:43 | Job in progress | 25/09/2024 | 12:03 | Job complete |
I was hoping I could use the Date in the string as a delimiter, use the time in the string as a delimiter, then the remaining text until the next date, and was hopeful that Power Query might do it, and/or that AI could recognise the date and time formats and split on each occurrence, however nothing I have tried has worked so far (mind you, my PQ and AI knowledge is limited!).
I would need to do this for several thousand rows of data, and for the process to be repeatable for new data each month.
Does anyone have any ideas how I could achieve this please?
Thanks in advance for your help.
PS the response I would give would be tell those creating the data "don't collect data like this(!)" or at least that they should have some sort a delimiter in between the progress stages, but its an extract from a CRM system, and there is nothing we can do about it unfortunately.
