title: Excel Macro for Leading Zeros with varying conditions
body: <p>I'm working on a macro to automate the process of preparing a large number (several thousand) excel workbooks into a standard format for importing into a database.</p>
  
  <p>One issue is adding leading zeros to some number/letter combinations.</p>
  
  <p>I have a temporary solution at the moment but one key fault it has is that it requires user input via dialog boxes to handle the procedure.  My goal was to eventually run the macro from commandline as a vbs.  </p>
  
  <p>It seems to me that the conditions I have are fairly ordinary but I am stumped on how to go about creating the macro code to handle it.  Perhaps it would be better implemented in Access during the import workflow.  However, having the excel files in the proper format is of additional benefit and why I decided to pursue a macro solution.</p>
  
  <p>There are two columns that need to be manipulated, both are set to text format by the macro.  </p>
  
  <p>Column A is a building number and needs to be 4 digits in length.  Currently they are formatted without leading zeros.  Some buildings have a single or combination of letters following the number.</p>
  
  <p>Some examples: <code>7</code>, <code>76</code>, <code>399</code>, <code>1010</code></p>
  
  <p>Some with letters: <code>76A</code>, <code>93B</code>, <code>812W</code></p>
  
  <p>A few are quirkier: <code>76A-G</code>, <code>812A-S</code></p>
  
  <p>These need to be reformatted to have leading zeros so: <code>0076A-G</code>, <code>0007</code>, <code>1000A</code>, etc.</p>
  
  <p>Column B is a filing number and is almost identical in nature to Column A with but additional details: <code>76-X-001</code>, <code>76A-X-023</code>, <code>76A-X-R-005</code>.  These need to become <code>0076-X-001</code>, etc.  Only the numbers before the first '-' need leading zeros, also to 4 digits in length.</p>
  
  <p>Currently I'm using two user input boxes at the start of the macro to set variables with the facility # and the leading zeros, than using find and replace to perform the update on the two columns.  This works just fine but has to be run for every number in the file.  Most files have only one or two numbers but some have 4-5.  It also means I can't automate this entire process and will need someone to sit and manually run the macro for each file.</p>
  
  <p>Anyone care to point me in the direction of a solution for automating this?  I looked at other leading zero solutions but they all seem to be aimed at numeric-only situations.</p>