VBA - Identifying Format and Using It as Condition

Question

I have a form where users are asked to input information into a field. The information comes in 1 of 3 formats: 12345-647-91 1234-5678-91 or 12345-6789-1

I am trying to create a macro that identifies the format and based on that outputs a new value. For example

  If format = XXXXX-XXX-XX Then
  output XXXXX-0XXX-00XX

  else if format = XXXX-XXXX-XX Then
  output 0XXXX-XXXX-00XX'

etc

Any guidance?

Do you want all outputs to be in the format XXXX-XXXX-XXXX and add zeros in front of any sets of digits smaller than four? Will these always be numbers or may letters also appear? — cybernetic.nomad
– cybernetic.nomad, Commented Jan 17, 2020 at 17:56
@cybernetic.nomad, yes a zero will be added in front of any set of digits less than 4. They will always be numbers. — JustHereChillin
– JustHereChillin, Commented Jan 17, 2020 at 18:14
@BigBen I am not having luck with Like operator because the input by used always has hyphens as should the output. When using Like the hyphens are giving me issues. — JustHereChillin
– JustHereChillin, Commented Jan 17, 2020 at 19:11
What should the output of 12345-647-91 be? What happens to the 5th digit? — BigBen
– BigBen, Commented Jan 17, 2020 at 21:02

BigBen · Accepted Answer · 2020-01-17 20:03:09Z

2

You can use Like for this:

Private Function myFormat(ByVal userInput As String) As String

    If userInput Like "#####-###-##" Then

        myFormat = Mid$(userInput, 1, 6) & "0" & _
                   Mid$(userInput, 7, 4) & "00" & _
                   Mid$(userInput, 8, 2)

    ElseIf userInput Like "####-####-##" Then

        myFormat = Mid$(userInput, 1, 10) & "00" & _
                   Mid$(userInput, 11, 2)

    ElseIf userInput Like "#####-####-#" Then
        ' and so on
    End If

End Function

Called like this:

Sub Test()
    Debug.Print myFormat("99999-999-99") '<~ returns 99999-0999-0099
    Debug.Print myFormat("9999-9999-99") '<~ returns 9999-9999-0099
End Sub

answered Jan 17, 2020 at 20:03

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BigBen Over a year ago

I'm sure you had to tweak it though, right? Because right now it doesn't add the leading zero in the case of ###-###-##. Though that's an easy fix.

JvdV · Accepted Answer · 2020-01-21 18:34:01Z

2

Here is another alternative:

Sub Test()

Dim arr1 As Variant: arr1 = Array("12345-123-12", "1234-1234-12", "12345-1234-1")
Dim arr2 As Variant
Dim Res As String

For Each el In arr1
    arr2 = Split(el, "-")
    Debug.Print Join(Array(Format(arr2(0), "00000"), Format(arr2(1), "0000"), Format(arr2(2), "0000")), "-")
Next

End Sub

edited Jan 21, 2020 at 18:34

answered Jan 17, 2020 at 20:45

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

2 Comments

BigBen Over a year ago

Uggh good eyes, I didn't even see that dropped digit. Maybe a typo?

JustHereChillin Over a year ago

Revised original typo in post.

Collectives™ on Stack Overflow

VBA - Identifying Format and Using It as Condition

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related