VBA to add leading o's and trailing space for fixed length SIR fields

Hi Guys,

I have to submit SIR reports for my work. Now each column header has to have a fixed length field with numeric fields I need to have leading zeros to make the fields up to a set length, 12 for example. and with Alpha fields I need to add trailing spaces to make up the length.

For example as stated above 1234 would become 000000001234, and if the alpha field need to be 10 characters then: Smith would become Smith[][][][][], where [] is a space.

Is it possible to have a VBA code that you simple select the 2nd cell in a column for example D2 (As D1 would obviously have the header and does not need altering) and then a message box asking how many characters need to be added, then whether this is leading or trailing, and only amend the the cells in the column with the selected range.

So basically whatever active cell I choose for a particular column it was add the necessary characters to complete the field requirements.

The reason I ask is that doing this manually is time consuming and laborious task. I know There would need to be at least 3 message boxes 1) to ask how many characters need adding, 2) what character this form takes either 0 or a space, 3) whether this should be leading or trailing.

I would really appreciate some VBA guru to help me out here as my VBA skills are of the bare minimum.

Thanks in advance for any help offered.

Wow that was a quick response! Thank you so much for your help.

I do have one question, does this macro check to see how many characters are in the cells. The reason I ask is because I have to collate from various agenecies and for example the length of their reference numbers vary from agency to agency. Does this code check how many characters are in a cell and then add the necessary amount of zeros or spaces to make up the required figure?

There is where I think at least one message box may be useful for example 'How many characters needed in this field?'

Do you think that would work?

Thanks again for all your help I really appreciate it.

EDIT: Also I have just run this macro and formated my column as text so that the leading zeros would appear and when I run the code the cells are converted back to a Number format and thus the leading 0's do not appear.

Is there a way to prevent this macro from altering the fomatting of the cells? A Paste Special perhaps? Thanks.

Hi all, is anyone albe to shed some light on my problem please? Thanks.

@Bob Phillips

Hi Bob,

Thanks again for all your help, I made a slight error in my first post when I said trailing spaces in alpha fields, it's actually alpha-numeric fields and they should be leading spaces for certain fields. Where in my previous example it should be [][][][][]014FKB instead of the; Smith[][][][][].

Forgive my ignorance at VBA, how would this now affect the code and could you point out what I would need to change in the code? Thanks.

Hi Bob,

I have uploaded a picture to illustrate what I need. I used the boxes to represent the spaces in column B. As you can see in Columns B & C; B needs to be 12 characters in length and have leading spaces and C needs 3 characted with leading zeros, rows 8-12 don't have to formatting applied and these I would have to manually change to match the formatting to the cells above.