Need help for formula to change layout of IBAN. I use 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.

Exsamplesssssss SP = space

Iban.

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

after the 89 i have to take the four digits and add up to 5 with a zero infront.
Then one space

the next four digits but add up to 8 digits with zeroes infront.
Then one space.
Then the account number 10 digits taken from behind
then one space.
And finally move the 69 in the end.

Thats the hard one i think.

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

after the first 5 digits. 5 digits and one space then the next 5 digits but add up to 8 digits with zeroes infront.
then space
then the last 12 digits.

Finally

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

After the 4 first digits its the next 5 digits. Then 11 spaces and then the last 11 digits.

I have attached a test sheet you can see on.

Thanks in advance

Abjac

[abjac]

HI I have played with it with my not so good skills in formulaes.
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)
But the other i have problem with. Need it to add zeroes and also to remove space

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 you formula gurues.

P.s Have cross posted it very important for me to solve this one.
http://www.mrexcel.com/forum/excel-q...ml#post3857146

Thanks in advance

P.s Its solved now in upper link at the cross posting. Answer can be seen there

Abjac