Need help with formulas. Excel 2003

[abjac]

Hi I need help to make a formula. I am not good to formulaes better to macroes, so any help will be great.

I need formulas for changing the layout of IBAN numbers in 3 countries. To format i can paste into a internal application.

Some i guess is easy for those who know, one maybe more complicated.

Spain: ES89 0049 1742 6927 1003 5721 or this format ES8900491742692710035721
Wanted result: 00049 00001742 2710035721 69

Italy. IT55M0306956151100000002129 or input maybe with space between. Wanted result.. 03069 00056151 100000002129 ..

Austria:
AT894478052742950000 or input with spaces between. Wanted result. 44780 52742950000

I think i solved the one to Austria. No zeroes there so the formula will look like this =MID(B2;5;5) &" "&RIGHT(B2;11)
The italien one: =MID(B3;6;5)&" "&MID(B3;11;5)&" "&RIGHT(B3;12) Need to get the zeroes here 03069 00056151 100000002129

And the same with the Spanish one. Leading zeroes and also here to remove spaces. =MID(B4;5;5)&" "&MID(B4;10;5)&" "&MID(B4;18;12)&" "&MID(B4;16;2) Please have a look
Ps Cross posted but not answers but very important for me to solve this one.

Need help for formula to change layout of IBAN. I use excel 2003

Thanks in advance

Abjac

 

[Andrew Poulsom]

For leading zeroes you can use a formula like:

TEXT(MID(B3;11;5);"00000000")

 

[sitewolf]

looks like you're on the right track to me....tho not sure where the 69 comes from at the end of the Spanish

for the 0s in the Italian, simply change " " to " 000" between the first 2 concatenations, yes?

 

[abjac]

HI Andrew. The code you gave works perfect. So thanks so much really need this code. SO its great

Sitewolf. The 69 is a checksum. and the reason why it have to be in the end, is that the internal application we use, there it has to be in the end.

Thanks to you also.

Have a nice day both
Sincely

Abjac