PDA

View Full Version : Excel VBA:ZeroPadding Mixed Number, Text, Number Content in Cell

sbubendorf
07-18-2008, 12:26 PM
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
07-18-2008, 01:00 PM
Try this:
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

Jimmy

sbubendorf
07-18-2008, 01:28 PM
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!!!!!

:2jump:

JimmyTheHand
07-18-2008, 01:44 PM
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).
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.