How to interrogate adjacent cells to create a concatenated string in a new cell

Hi, I would like some help to create a single concatenated string that shows the opening times of different stores. I have attached an example.

Many thanks.

Hi DILIPandey,

Sorry if I was not clear in my question or example.

The expected result is what I have shown in Column I "Opening Hours". (I had to do these manually)

This example is only a small sample of a very large worksheet (over 1000 stores). The reason I only want the concatenated string for the opening hours of each store, as shown in "Opening Hours", is because this information will appear in a summary worksheet.

So the summary worksheet will show:

Store 1 - Concatenated Opening hours xxxx
Store 2 - Concatenated Opening hours xxxx
Store 3 - Concatenated Opening hours xxxx
Etc....

Thanks

Fred

How does one know what days the stores are open!

Hi Kevin,

If you take a look at the attached example spreadsheet, Column B to H are the days of the week (Column B=Mon, Column C=Tue, Column D=Wed, Column E=Thu, Column F=Fri, Column G=Sat, Column H=Sun)

As an example look at Row 3 Cumbria:

Opening times are: - (B3) Mon 080000-220000, (C3) Tues 080000-220000, (D3) Wed 080000-220000, (E3) Thu 080000-220000, (F3) Fri 080000-220000, (G3) Sat 073000-220000, (H3) Sun 100000-160000

So I want the concatenated string in cell C3 "Opening Hours" to read - "Our Standard Opening hours are: Mon To Fri 080000-220000, Sat 073000-220000, Sun 100000-160000"

Please note each store has different opening times on different days.

Thanks

Hi Kevin,

Perhaps I was not clear,

I have just used these stores as a example (there are over 1000 of them). I have shown the required concatenated string for each store (I entered this manually) in Column "I" "Opening Hours"

You can move Column "I" further along or just delete it. I just need to know how to reproduce this concatenated string in Column "I" for each store by using a function or formula or whatever other automated method. Once I know how you have done this, I can then use that method for my 1000+ stores.

So there is no need for you to know the opening hours of any other store. I just need to know how to do it with the sample I have provided and then can take that away and use it with my larger live spreadsheet.

Thanks

Hi Fred

For I2:

Formula:

Please Login or Register  to view this content.

This will work for I2,I3,I7,I9,I10

Hi Kevin,

That answer is correct, but I had already done that to produce my output previously (although I've only shown the data in this example and not the formula).

Using this method means that I have to interrogate each store entry manually and then create then create each store its own concatenated formula. Not a problem for a few stores, but when there is 1000+ it becomes a nightmare. As each store has different opening hours and days. What I want is a method that will interrogate each store automatically and the put together the concatenation string

Thanks

Yes, but if column I is empty, how will one know what the criteria is for the opening times!

Hi Kevin,

Thank you for your patience - but I still think you misunderstand.

All the stores opening times for each day are available columns B to H.

The formula / function / automation which I am looking for will create the data in Column "I", using he the opening times for each store columns B to H.

So using Row 3 as an example:

Column A(Store) Column B(Mon) Column C(Tue) Column D(Wed) Column E(Thu) Column F(Fri) Column G(Sat) Column H(Sun) Column I (Opening Hours) Concatenation created using some automated method
Cumbria 080000-220000 080000-220000 080000-220000 080000-220000 080000-220000 073000-220000 100000-160000 Our Standard Opening hours are: Mon To Fri 080000-220000, Sat 073000-220000, Sun 100000-160000

Store Name (Column A - Details already known), Mon Opening (Column B - Details already known), Tue Opening (Column C - Details already known), Wed Opening (Column D - Details already known), Thu Opening (Column E - Details already known), Fri Opening (Column F - Details already known), Sat Opening (Column G - Details already known), Sun Opening (Column H - Details already known), Store Opening Hours (Column I - Concatenation string made up of details from Column B to H. I am looking for some sort of formula / function / automation to create this)

So if you take the example you provided me earlier, the concatenation is correct - but you had to manually interrogate each store individually to create it. I want to automate the interrogation and DO NOT want to create the concatenation manually. There are too many stores and it will lead to some mistakes.

Thanks