Check Postcode for Correct Character Zero (0) and not Letter (O)

[razzandy]

Hi Guys, so I have the below Formula where it correctly formats a postcode but now I need it to check the Characters to make sure a Zero (0) is in fact a zero and not a Letter (o)? So a UK Postcode example could be BL1 oLL, so the 'o' in this example would need changing to Zero. As you can imagine it gets more complicated with longer/shorter postcodes! UK postcode formats can be found here: Postcodes in the United Kingdom - Wikipedia.

=UPPER(REPLACE(SUBSTITUTE(B5," ",""),LEN(SUBSTITUTE(B5," ",""))-2,0," "))

Thanks in advance!

 

[Scott Huish]

Can't you just Replace all Os with 0s?
=SUBSTITUTE(B5,"O",0)

and perhaps for the whole thing:
=REPLACE(SUBSTITUTE(SUBSTITUTE(B5," ",""),"O",0),4,0," ")

 

[MARK858]

Can't you just Replace all Os with 0s?
=SUBSTITUTE(B5,"O",0)

Hi Scott, no because there are postcodes like PO20 0AA