How to stop excel from concatenating blank cells

[toddb511]

Hi, I am preparing a worksheet to easily enter opening hours into our backend:


Year Month Day Hours
1 2021 12 24 09:00-15:00
2 2021 12 25 Closed
3 2022 1 1 Closed
4
5
6
7
8
9
10


I am using the following concatente formula for the whole sheet:

=CONCATENATE(B2,"-",C2,"-",D2,"=",E2,";",B3,"-",C3,"-",D3,"=",E3,";",B4,"-",C4,"-",D4,"=",E4,";",B5,"-",C5,"-",D5,"=",E5,";",B6,"-",C6,"-",D6,"=",E6,";",B7,"-",C7,"-",D7,"=",E7,";",B8,"-",C8,"-",D8,"=",E8,";",B9,"-",C9,"-",D9,"=",E9,";",B10,"-",C10,"-",D10,"=",E10,";",B11,"-",C11,"-",D11,"=",E11,";")

to get the following output:

2021-12-24=09:00-15:00;2021-12-25=Closed;2022-1-1=Closed;--=;--=;--=;--=;--=;--=;--=;

I can't figure out how to have excel skip a cell if it is blank; i.e. I don't want the "--=;--=;--=;--=;--=;--=;--=;" if someone doesn't fill in all possible days.

See attachment.

Thank you for your help!

[menem]

this function may help.

=SUBSTITUTE( old_formula, "--=;" , "")

Regards.

[JohnTopley]

Try

=SUBSTITUTE(CONCATENATE(B2,"-",C2,"-",D2,"=",E2,";",B3,"-",C3,"-",D3,"=",E3,";",B4,"-",C4,"-",D4,"=",E4,";",B5,"-",C5,"-",D5,"=",E5,";",B6,"-",C6,"-",D6,"=",E6,";",B7,"-",C7,"-",D7,"=",E7,";",B8,"-",C8,"-",D8,"=",E8,";",B9,"-",C9,"-",D9,"=",E9,";",B10,"-",C10,"-",D10,"=",E10,";",B11,"-",C11,"-",D11,"=",E11,";"),"--=;","")

[Greg M]

Hi there,

Take a look at the attached version of your workbook and see if it does what you need. It uses a VBA function with the following code:

Please Login or Register  to view this content.

An advantage of this approach is that no code modifications are required if the data range is increased or decreased vertically.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[toddb511]

Thanks for your replies! I used the SUBSTITUTE option as that was the most straightforward and least hassle. It works perfectly for my purposes. Thanks again