Condition as to cater with preset value in a forma

Question

My previous VBA code is like this

field9 = 00 & Format(Date, "YYYYMM") & CleanString(myVal) & _
         Format(Cells(vRow, 8).Value, "00000")

This field9 variable will be used to convert my excel numbers in a field from 000000000000006(preset value) to 02017010100006 (from what i've set in field 9). The CleanString(myVal) i use to pass autogenerate sequence number from other module.

Currently i need to modify this thing in order to cater with my new requirement which is 002017010000006( preset value) to 002017010100006. I need to remain the preset value as i can't use my current code because it will have more digits. I need assistance for this.

i will further explain what the problem is. The first part is what my variable are meant to do

000000000000006(pulled this from text file) to 002017010100006(vba code processed)

"00""201701""01""00006"
1. preset 00 in my code
2. 201701 is YYYYMM
3. myval is my running sequence number 01-99
4. i format my cell into 00000 let say if this field having 1 then it will be 00001 5. i can do this using my current formula

The problem now is

002017010000006(pulled this from text file) to 002017010100006(vba code processed) 

1. This 002017010000006 is from a text file too
2. if i am going to use my current formula, i will having this kind of issues as it will be more than 15 digits 00201701012017010013676

i need solution in maintaining "201701" from my text files instead of using Format(Date, "YYYYMM") from my current solutions

This is the outcome i need 002017010100006 just 15 digits

p/s: i have edited my info for further understanding, as i have not cleared with my question and there's some wrong info in this too. i am sorry for the inconvenience

Thank you in advance.

Answer 1

Try this:

Function Transform(initial As String) As String
    Transform = Left(initial, 8) & CleanString(myVal) & Format(Cells(vRow, 8).Value, "00000")
End Function

or simply assign it to a variable...

newString = Left(oldString, 8) & CleanString(myVal) & Format(Cells(vRow, 8).Value, "00000")

Be aware that your CleanString(myVal) has to be a string of length 2. If it has any length, it still needs some modification (i.e. with a 3 digits value, the final number will be on 16 digits)

So if you want to allow that CleanString have more than two digits, do this to keep always a 15 digits length:

    Dim s As String: s = CleanString(myVal)
    newString= Left(oldString, 8) & s & Format(Cells(vRow, 8).Value, String(7 - Len(s), "0"))