Hi all,
I'm wondering if it is programatically possible to find an arrangement of text within cells, then insert text at a given point that uses text already in the cell. Let me explain.
I have the following as a typical cell content - it is opening hours for a business. The issue is that on certain days, there may be two or three opening times.
Monday,12:00,14:00,17:00,21:30|Tuesday,12:00,14:00,17:00,21:30|Wednesday,12:00,14:00,17:00,21:30|Thursday,12:00,14:00,17:00,21:30|Friday,12:00,14:00,16:30,21:30|Saturday,12:00,14:00,16:30,21:30|Sunday,17:00,21:30
Here, for example, the business is open on Monday from 12 to 2pm, then from 5 to 9:30pm.
What I need to be able to do is find the occasions where there are two (or rarely three) sets of opening times like this, and insert the "Day" and a separator, so I end up with the hours formatted like this:
Monday,12:00,14:00|Monday,17:00,21:30|Tuesday,12:00,14:00|Tuesday,17:00,21:30|Wednesday,12:00,14:00|Wednesday,17:00,21:30|Thursday,12:00,14:00|Thursday,17:00,21:30|Friday,12:00,14:00|Friday,16:30,21:30|Saturday,12:00,14:00|Saturday,16:30,21:30|Sunday,17:00,21:30
So the common factor is that in the original data, there are two times followed by another comma, whereas a pipe indicates the start of another day.
Within the dataset I have, there's a great range of which days have more than one set of hours, some there is only one day, some there is all seven.
Anyway that's the situation, if you think there's a formula or a vba that would be able to do what I want, I'd like to know more!
Just the same, if you think it's not possible, please let me know that too
Richard
I'm wondering if it is programatically possible to find an arrangement of text within cells, then insert text at a given point that uses text already in the cell. Let me explain.
I have the following as a typical cell content - it is opening hours for a business. The issue is that on certain days, there may be two or three opening times.
Monday,12:00,14:00,17:00,21:30|Tuesday,12:00,14:00,17:00,21:30|Wednesday,12:00,14:00,17:00,21:30|Thursday,12:00,14:00,17:00,21:30|Friday,12:00,14:00,16:30,21:30|Saturday,12:00,14:00,16:30,21:30|Sunday,17:00,21:30
Here, for example, the business is open on Monday from 12 to 2pm, then from 5 to 9:30pm.
What I need to be able to do is find the occasions where there are two (or rarely three) sets of opening times like this, and insert the "Day" and a separator, so I end up with the hours formatted like this:
Monday,12:00,14:00|Monday,17:00,21:30|Tuesday,12:00,14:00|Tuesday,17:00,21:30|Wednesday,12:00,14:00|Wednesday,17:00,21:30|Thursday,12:00,14:00|Thursday,17:00,21:30|Friday,12:00,14:00|Friday,16:30,21:30|Saturday,12:00,14:00|Saturday,16:30,21:30|Sunday,17:00,21:30
So the common factor is that in the original data, there are two times followed by another comma, whereas a pipe indicates the start of another day.
Within the dataset I have, there's a great range of which days have more than one set of hours, some there is only one day, some there is all seven.
Anyway that's the situation, if you think there's a formula or a vba that would be able to do what I want, I'd like to know more!
Just the same, if you think it's not possible, please let me know that too
Richard
