Adding zeroes to document number based on the type of document.

  • I am tasked with renumbering certain documents based on their type. We have two types - 'H' and 'P'. If the document type is P, there is no modification required. But if it is H, modification is required. Tpe H has three segments all numerical and is without a suffix in some cases and has suffixes in the form of .x, -x and (x). Example:



    The third segment and/or suffixes could be 2 digits. Refer S.No 7 & 8 in the picture below:


    The text strings following the number segments are of varying length.

    Currently I am doing this manually, but itt is extremely tedious and I have thousands of documents to complete. I have broken my head trying formulas and have been unsuccessful. Will be grateful if someone can help me. I don't know VBA but a willing to learn

    The requirements are:


    1. For H Type documents all number segments should be prefixed with zero numeral except where there are two digits. See "Modified Number" column S.No 7 & 8
    2. No change for P Type documents.

    I am attaching the Excel file from which I made the image in this message.

  • You could try something like a user defined function, written in VBA as per attached example.

    User defined function is called AddZero in this example.


    Number Modification.xlsm

    Thank you so much. This is brilliant. However I am unable to see the VBA code. I understand if you do not want to reveaal the code, but how do I copy the function to the worksheet where it is needed/

  • You are welcome.


    The code is not hidden, the macro called AddZero can be found in Module 1, this is where VBA code is stored.


    Open the spreadsheet and press the Alt + F11 key combination to see the macros:


    In order to use this in another spreadsheet, you will need to familiarise yourself with creating Modules and macros in VBA.


    Then you will be able to copy the code into any spreadsheet as long as you save the output as a macro enabled spreadsheet (typically an .xlsm file).


    I have just picked the following link as an introduction:

    https://support.microsoft.com/…32-4259-9177-a71f7e626de0

  • Thank you so much again. The module view was not on.
    It is a little late in life for me to learn new things, old dog cannot learn new tricks :D:D:D
    But my current job will require lot of manipulation in Excel. So I have to learn VBA. I will try.