logical problem: if 4 first filled then enter if one of next four is filled

[littlepete]

hello

i have a logical problem...
my cel contains present address BP-BQ-BR-BS ; old address BT-BU-BV-BW ; phone numbers BB-BC-BD and then emails and websites...
those last two give no problem...

i start with putting the present address (street and number ; postal number ; city ; country)
if any of those four were not empty then there should follow an entry ( char(10) ) if one of the first columns for the old address is filled...

after the addresses, phone numbers: same system:
if any of the data are filled (present address, old address: 8 columns) AND if one the three phone numbers is filled then there should
follow a new line and after that the phone numbers...

problem:

if no data are filled before the phone numbers, there still comes a new line (for no use) before the first phone number...

this is the formula:

=ALS(EN(BP5="";BQ5="";BR5="";BS5="");"";"☛ ①"&TEKEN(150)&"ADRES: "
&ALS(BP5<>"";BEGINLETTERS(BP5)&" ";"")
&ALS(EN(BR5<>"";BP5<>"");" - ";"")
&ALS(BQ5<>"";BQ5&TEKEN(150);"")
&ALS(BR5<>"";HOOFDLETTERS(BR5)&" ";"")
&ALS(BS5<>"";"["&HOOFDLETTERS(BS5)&"]";""))

&ALS(OF(BP5<>"";BQ5<>"";BR5<>"";BS5<>"");
ALS(OF(BT5<>"";BU5<>"";BV5<>"";BW5<>"");
TEKEN(10)&" ②"&TEKEN(150)&"VORIG ADRES: "
&ALS(BT5<>"";BEGINLETTERS(BT5)&" ";"")
&ALS(EN(BV5<>"";BT5<>"");" - ";"")
&ALS(BU5<>"";BU5&TEKEN(150);"")
&ALS(BV5<>"";HOOFDLETTERS(BV5)&" ";"")
&ALS(BW5<>"";"["&HOOFDLETTERS(BW5)&"]";"");"");"")


&ALS(OF(BP5<>"";BQ5<>"";BR5<>"";BS5<>"";BT5<>"";BU5<>"";BV5<>"";BW5<>"");
ALS(OF(BB5<>"";BC5<>"";BD5<>"");
TEKEN(10)&"☛ "
&ALS(BB5<>"";"GSM "&BB5;"")
&ALS(BC5="";"";(ALS(BB5<>"";" - ☎ "&BC5;"☎ "&BC5)))
&ALS(BD5="";"";ALS(OF(BB5<>"";BC5<>"");" - INT "&BD5;"INT "&BD5));"");"")


can anyone see where the logical answer goes wrong?

tomorrow is the last day of 2021 ... i wish you all patience and luck and most of all inspiration in 2022 !!!

 

[GraH]

Beste landgenoot,

Je kan hier misschien wat inspiratie opdoen en alternatieve functies proberen. Ken ze wel niet in het Nederlands. Niet helemaal wat je wenst, maar het principe zou wel duidelijk moeten zijn (hoop ik).

Book1
	B	C	D	E	F	G	H	I
1	straat	nummer	postcode	gemeente	land	GSM	Tel
2	Boulevard	121	1000	Brussel Stad	België		022011112	Boulevard 121 1000 Brussel Stad België ?: ☎: 022011112
3	avenue			Leuven		0499887766		avenue Leuven ?: 0499887766 ☎:
Sheet1

Cell Formulas
Range	Formula
I2:I3	I2	=TEXTJOIN(CHAR(10),FALSE,TEXTJOIN(CHAR(10),FALSE,B2:F2),TEXTJOIN(CHAR(10),FALSE,SWITCH($G$1:$H$1,"GSM","?: "&G2,"Tel","☎: "&H2)))