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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Watch MrExcel Video

Forum statistics

Threads
1,127,597
Messages
5,625,728
Members
416,130
Latest member
galgozzi

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