Find and insert

stretchr

New Member
Joined
Aug 27, 2009
Messages
8
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,659
Messages
5,838,642
Members
430,558
Latest member
Krampus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top