Excel VBA:ZeroPadding Mixed Number, Text, Number Content in Cell

[sbubendorf]

I have cells containing text such as 100P1, 10P1, 1P1, 100PB10, etc. I would like to pad the leading set of numbers to 3 places and the trailing set of numbers to 3 places by adding leading zeros where needed to each set of numeric characters. Note that the length of the text portion of the string can vary in length.

Can anyone help me by providing a function to accomplish this, or is that beyond the scope of the forum?

Thank you for any help that can be provided!

[JimmyTheHand]

Try this:
[vba]Function Test(Inp As String)
Dim s As String, i As Long
s = Inp
For i = 0 To 9
s = Replace(s, CStr(i), "")
Next
Test = Right(1000 + Left(Inp, InStr(Inp, s) - 1), 3)
Test = Test & s
Test = Test & Right(1000 + Mid(Inp, InStr(Inp, s) + Len(s)), 3)
End Function
[/vba]

Jimmy

[sbubendorf]

Beautiful !! Thanks, Jimmy !! It does exactly what I wanted. Now I'll study it and see if I can figure out how it works, so I might not have to ask for the entire thing the next time. I'd been searching through all kinds of parsing routines trying to find some pieces of code or methods that I could combine, but I'd spent hours searching and hadn't come up with anything that I could decipher well enough to make adjustments. Thank you!!!!!

[JimmyTheHand]

Welcome
The logic behind the code is this:

• Cut the leading and trailing numeric part off the string, by replacing every numeric characters with an empty string.
• The remainig string is the core alphabetic part (variable s in the code).
• Pad the numeric part before the core with leading zeros
• Pad the numeric part after the core with leading zeros
• Concatenate the 3 strings (i.e. the padded leading part, the core and the padded trailing part)

Note: In order to obtain the core part, the algorithm removes ALL numeric characters from the string. If there are digits inside the core, they are removed as well, and so the function will fail.


Padding a number with leading zeros goes this way:

• Add 10^N to the number, where N is the number of zeros. E.g. for 3 zeros, add 1000.
• Convert the result to string.
• Take the rightmost N characters of the string.

Note: In VBA, type conversions go automatically, if applicable. So the 2nd step can be skipped most of the time.

HTH

Jimmy

[sbubendorf]

Jimmy,

Thank you for the explanation, too !