How to use concatenate to group times (hh:mm) and retain time format.

[Obwan]

I have a list of opening hours which need to be entered on our website in a format different than the way the user enters the information on their excel form.

The format on the user form is:

Row 1 – Column 1: Mon, Column 2: 8:00AM, Column 3: 5:30 PM
Row 2 – Column 1: Tues, Column 2: 8:00AM, Column 3: 5:30 PM
Etc.
I need to enter it as
Mon 8:00 AM – 5:30 PM|Tues 8:00 AM – 5:30 PM |etc
I initially tried to use = CONCATENATE(A35," ",B35," - ",C35,"|",) the result is Mon 0.333333333333333 - 0.729166666666667|
I would like to copy and paste. Is there a way to get the format I need without manually typing it out?

Thank you in advance.

 

[Obwan]

Mon 08:00 AM-05:30 PM | Tue 08:00 AM-05:30 PM | Wed 08:00 AM-05:30 PM | Thu 08:00 AM-05:30 PM | Fri 08:00 AM-05:30 PM | Sat 08:00 AM-01:00 PM | Sun Closed- Closed |

I don't care where the data wraps when I paste it. I just want it continuous. I tried , = , another &. best result "False"

 

[rondeondo]

Hey Obwan

=A35&" "&TEXT(B35,"hh:mm am/pm")&"-"&TEXT(C35,"hh:mm am/pm")&" |"
=A36&" "&TEXT(B36,"hh:mm am/pm")&"-"&TEXT(C36,"hh:mm am/pm")&" |"

TEXT(C35,"hh:mm am/pm") the text between the "'s is the format for the value, can't add other text to that.
Any time you want to join the values of two or more cells together you can use the & as a text join.
In the current version of excel they have also included textjoin() as a new function. It allows you to join a series of cells and specify a text separator, much better than the old concatenate()

So if you had data in D1 to D7 you wanted to join with a | between you could use:

=textjoin("|",TRUE,D1:D7)

The true / false option in the middle is for ignoring blanks

 

[Obwan]

Hey Obwan

=A35&" "&TEXT(B35,"hh:mm am/pm")&"-"&TEXT(C35,"hh:mm am/pm")&" |"
=A36&" "&TEXT(B36,"hh:mm am/pm")&"-"&TEXT(C36,"hh:mm am/pm")&" |"

TEXT(C35,"hh:mm am/pm") the text between the "'s is the format for the value, can't add other text to that.
Any time you want to join the values of two or more cells together you can use the & as a text join.
In the current version of excel they have also included textjoin() as a new function. It allows you to join a series of cells and specify a text separator, much better than the old concatenate()

So if you had data in D1 to D7 you wanted to join with a | between you could use:

=textjoin("|",TRUE,D1:D7)

The true / false option in the middle is for ignoring blanks

Thank you rondeondo, will have a go at this.
Perfect, I just did this. I created the 7 individual formulas then used the textjoin to merge them all into 1 line.
I will hide the 7 lines and just show the textjoin result, which I will use to copy and paste.
Thank you for your help, knowledge and patience.