Find, replace, substitute

[cunningAce]

Hi All,

Hoping you can help me please,

I have several combinations of product codes that I want to change so that all share the same format.

Results should be to keep the first 2 letters,
Next a 4 digit number, so in some of the below examples 53 would become 0053, or 331 would become 0331 (add leading 0's to make the 4 digits)
Finally Remove the letter 'V' and replace with 0's. So 'V01' becomes 0001, or 'V12' becomes 0012.

"2 letters","4 numbers","4 numbers" to make the new 10 character code.

Examples of start and end results,​

[TABLE="width: 224"]
<colgroup><col span="2"><col></colgroup><tbody>[TR]
[TD]FB1344V01[/TD]
[TD][/TD]
[TD]FB13440001[/TD]
[/TR]
[TR]
[TD]SK53V02[/TD]
[TD][/TD]
[TD]SK00530002[/TD]
[/TR]
[TR]
[TD]LG331V03[/TD]
[TD][/TD]
[TD]LG03310003[/TD]
[/TR]
[TR]
[TD]PL192V12[/TD]
[TD][/TD]
[TD]PL01920012
[/TD]
[/TR]
</tbody>[/TABLE]

Hoping for a formula to achieve this is possible,
Thanks in advance.

 

[steve the fish]

Provided your examples are representative then this works:

=LEFT(A1,2)&TEXT(SUBSTITUTE(MID(A1,3,99),"V","00"),"00000000")

 

[cunningAce]

Nice solution, thanks for the help!