How do I achieve this formating with vba?

[acombest]

I know how to do this within excel but now I need to do it with VBA and im not sure how to approach this.
I have a series of numbers for example 0409-792-1
I need to make sure that the 1st set always has 5 digits the 2nd has 4 and the 3rd has 2.
If it does not then I need to add zeros on the front of the series so it would look like 00409-0792-01
This series of numbers will be found in cell Db.Cells(I,2).Value

If any additional information is needed please let me know.
Thank you

 

[kbrummert]

This is very basic. It only accounts for the situation you describe and does not check for other things like non-numeric (abcd-efg-h), less than two dashes (1233-567) or something completely different ("not applicable")...

Function fnc_format(val As String)

Dim ary() As String

Dim str0 As String
Dim str1 As String
Dim str2 As String


ary = Split(val, "-")

' check for length of first
str0 = ary(0)
str1 = ary(1)
str2 = ary(2)


If Len(str0) < 5 Then str0 = Application.WorksheetFunction.Rept("0", (5 - Len(str0))) & str0
If Len(str1) < 4 Then str1 = Application.WorksheetFunction.Rept("0", (4 - Len(str1))) & str1
If Len(str2) < 2 Then str2 = Application.WorksheetFunction.Rept("0", (2 - Len(str2))) & str2

fnc_format = str0 & "-" & str1 & "-" & str2

End Function