EMBEDDED IF/THEN function with multiple AND/OR conditions

Hello,

I have a difficult scenario which I cannot wrap my head around, I hope there's an Excel PRO here who can help me!

It has to do with editing and formatting telephone numbers and country codes.

Here it is:


EMBED:
C2 = IF A2=France AND IF B2 has 9 digits AND IF B2 starts with 6, THEN add "33" to the beginning of B2, otherwise copy as is
C2 = IF A2=Belgium AND IF B2 has 9 digits AND IF B2 starts with 47 OR 48 OR 49, THEN add "32" to the beginning of B2, otherwise copy as is
C2 = IF A2=United Kingdom AND IF B2 has 10 digits AND IF B2 starts with 71 OR 73 OR 74 OR 75 OR 77 OR 78 OR 79, THEN add "44" to the beginning of B2, otherwise copy as is

etc... there are a lot of countries with different conditions

HOW DO I EMBED SO MANY CONDITIONS ???

I would really really appreciate any help !

Thanks to all

Eli

Probably some sort of table(s) LOOKUP (Country, Length of number, "Starts with .." values ,Prefix(es) (44, 33 etc).

Have you (or can you create) such a table? Or reasonable sample covering extremes?

=IF(AND(A3="FRANCE",LEN(B3)=9,VALUE(LEFT(B3,1))=6),"33"&B3,IF(AND(A3="BELGIUM",LEN(B3)=9,(OR(VALUE(LEFT(B3,2))=47,VALUE(LEFT(B3,2))=47,VALUE(LEFT(B3,2))=49))),"44"&B3,B3))

the trick is to break it down.

Where I have to build complex nested if statements I use a blank sheet and write each element in and build it up slowly

easiest thing to do is remember that each different 1sst element (in this case country) would need its only IF statement to be places in the If False clause of the previous statement. (ie. if X = X, do this, else do this)
then use a mixture of AND(case1, case2, case3) and OR(case1, case2, case3) to build up the rest.

Kramxel - Thank you, very very helpful. I figured out how to do it thanks to your advice.
All the best!