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

littlepete

Active Member
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(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 !!!

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

GraH

Well-known Member
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
BCDEFGHI
1straatnummerpostcodegemeentelandGSMTel
2Boulevard1211000Brussel StadBelgië022011112Boulevard 121 1000 Brussel Stad België ?: ☎: 022011112
3avenueLeuven0499887766avenue Leuven ?: 0499887766 ☎:
Sheet1
Cell Formulas
RangeFormula
I2:I3I2=TEXTJOIN(CHAR(10),FALSE,TEXTJOIN(CHAR(10),FALSE,B2:F2),TEXTJOIN(CHAR(10),FALSE,SWITCH(\$G\$1:\$H\$1,"GSM","?: "&G2,"Tel","☎: "&H2)))

Replies
0
Views
831
Replies
1
Views
309
Replies
0
Views
375
Replies
2
Views
1K
Replies
0
Views
804

1,191,373
Messages
5,986,271
Members
440,015
Latest member
knijgh

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?

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

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