Help - add zero to only rquisition with five digitis

[bllanko]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CTEMP%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <o:DocumentProperties> <o:Author>KBR I.T.</o:Author> <o:Version>11.9999</o:Version> </o:DocumentProperties> </xml><![endif]--><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} span.EmailStyle15 {mso-style-typeersonal; mso-style-noshow:yes; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt; font-family:Arial; mso-ascii-font-family:Arial; mso-hansi-font-family:Arial; mso-bidi-font-family:Arial; color:windowtext;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> HELLO<o></o>
<o> </o>
I need some help please…I have around 3000 requisition numbers on column A, which starts with letter A and had from one to 9 digit numbers after. However what I need is in column B to add a zero (0) to the only requisition that starts with A and has only five digit numbers after (see below).<o></o>
<o> </o>
Please see below example:<o></o>


<table x:str="" style="border-collapse: collapse; width: 168pt;" border="0" cellpadding="0" cellspacing="0" width="224"><col style="width: 61pt;" width="81"> <col style="width: 48pt;" width="64"> <col style="width: 59pt;" width="79"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl33" style="height: 12.75pt; width: 61pt;" width="81" height="17">Column A</td> <td rowspan="9" class="xl30" style="border-bottom: 1pt solid black; width: 48pt;" width="64"> </td> <td class="xl34" style="border-left: medium none; width: 59pt;" width="79">Column B</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">A23664</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">A023664</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">A69876</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">A069876</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">A986489</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">A986489</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">A148976</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">A148976</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl26" style="height: 12.75pt; border-top: medium none;" height="17">A6986</td> <td class="xl27" style="border-top: medium none; border-left: medium none;">A6986</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">A48973</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">A048973</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; border-top: medium none;" height="17">A48766</td> <td class="xl25" style="border-top: medium none; border-left: medium none;">A048766</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl28" style="height: 13.5pt; border-top: medium none;" height="18">A46897</td> <td class="xl29" style="border-top: medium none; border-left: medium none;">A046897</td> </tr> </tbody></table>

 

[VoG]

Try

=IF(LEN(A1)=6,"A0"&RIGHT(A1,5),A1)

 

[Haseeb Avarakkan]

Try,

=IF(AND(LEFT(A1)="A",LEN(A1)=6),REPLACE(A1,2,0,0),A1)

Copy down...

 

[bllanko]

thnaks a lot, both formula works.

thanks again