Format combination of text and number string

[rakotonirinas]

Hello there,

I'm trying to format a string which consists of 20 numbers and one letter OR a 21 alphanumeric numbers only to a specific format with VBA on a form but unsure how to go about in doing it.
When I receive the data, it gives me a very long 21 characters string and I need to format it to something like,
either [if with letter] ## ### #X# ## #### #### ### [X represents the letter] or [if no letter] ## ### ### ## #### #### ###.

Is there any way I can format the string from either by looking at whether there is a letter or not. I know how to do it if it was just a string of 21 numbers but the existence of the letter doesn't make it easy.

Thank you very much.

[rakotonirinas]

Just a quick update.
I thought creating a customer UDF may help for this exercise but unsure how I can now incorporate it to a Subroutine.
Any help would be appreciated.

Public Function IsLetter(strValue As String) As Boolean
    Dim intPos As Integer
    For intPos = 1 To Len(strValue)
        Select Case Asc(Mid(strValue, intPos, 1))
            Case 65 To 90, 97 To 122
                IsLetter = True
            Case Else
                IsLetter = False
                Exit For
        End Select
    Next
End Function

[MrShorty]

It appears to me that you are starting with a 21 character text string, and you are trying to manipulate the text string into a space delimited text string with the 21 characters separated into "groups" with spaces between. The presence/absence of the non-numeric character in the text string does not seem to change the desired spacing/grouping. Assuming those observations are correct, I would expect some simple text manipulation functions would be able to do this:

1) Left(text,2) will get the first two characters.
2) Mid(text,3,3) will get the next three characters.
3) Mid(text,6,3) will get the next three characters. (and so on with Mid() functions to extract the different "groups")
4) Right(text,3) will get the last three characters.
5) Join the individual text strings together with a space between each "group." In VBA, if you store the above "groups" into an array, then you can use the Join() function https://learn.microsoft.com/en-us/of.../join-function Otherwise, just use the concatenate operator to combine the individual text strings together

In VBA, this might look like:

Dim textarray(0 to 6) as string, inputtext as string, outputtext as string
inputtext="123456S89012345678901"
textarray(0)=Left(inputtext,2)
textarray(1)=Mid(inputtext,3,3)
and so on
textarray(6)=Right(inputtext,3)
outputtext=Join(textarray," ")

Will something like that work for you, or are you required to use "formatting" (like a Format function or Excel number formatting)?

[Artik]

Try it

Sub AAA()
    Dim MyString    As String
    Dim strTmp      As String
    Dim strChar     As String
    Dim i           As Long
    Dim v           As Variant

    v = Array("123456789012345678901", "123456A78901234567890")

    For i = 0 To UBound(v)
        MyString = v(i)

        If IsNumeric(MyString) Then
            strTmp = Format(MyString, "## ### ### ## #### #### ###")
            Debug.Print "Input: " & MyString
            Debug.Print "Output: " & strTmp
        Else
            strChar = Mid(MyString, 7, 1)
            If strChar Like "[A-z]" Then
                strTmp = Replace(MyString, strChar, 7)
                strTmp = Format(strTmp, "## ### ### ## #### #### ## #")
                Mid(strTmp, 9, 1) = strChar
                Debug.Print "Input: " & MyString
                Debug.Print "Output: " & strTmp
            End If
        End If
        Debug.Print
    Next i

End Sub

Artik

[rakotonirinas]

Thank you both for your suggestion. I believe both suggestions will do the work.
@Artik, for the v Array, can the value inside be the value in a textbox of a form, i.e., whichever the string value inside (21 characters, it will assess first whether it is a full numerical value or the one with a text and then create the group accordingly?

[rakotonirinas]

So this is the full subroutine which will check the length of the string inside the DB01 textbox first before grouping them according to the found length. I have included @Artik's suggestion on here but I think the v = Array one is not properly written.

Private Sub DB01_AfterUpdate()

Dim MyString    As String
Dim strTmp      As String
Dim strChar     As String
Dim i           As Long
Dim v           As Variant

If Len(Application.WorksheetFunction.Substitute(Me.DB01.Value, " ", "")) = 21 Then

    v = Array(Me.DB01.Value)

    For i = 0 To UBound(v)
        MyString = v(i)

        If IsNumeric(MyString) Then
            strTmp = Format(MyString, "## ### ### ## #### #### ###")
            Debug.Print "Input: " & MyString
            Debug.Print "Output: " & strTmp
        Else
            strChar = Mid(MyString, 7, 1)
            If strChar Like "[A-z]" Then
                strTmp = Replace(MyString, strChar, 7)
                strTmp = Format(strTmp, "## ### ### ## #### #### ## #")
                Mid(strTmp, 9, 1) = strChar
                Debug.Print "Input: " & MyString
                Debug.Print "Output: " & strTmp
            End If
        End If
        Debug.Print
    Next i

ElseIf Len(Application.WorksheetFunction.Substitute(Me.DB01.Value, " ", "")) = 13 Then
    Me.DB01.Value = Format(Me.DB01.Value, "00 0000 0000 000")
ElseIf Len(Application.WorksheetFunction.Substitute(Me.DB01.Value, " ", "")) = 11 Then
    Me.DB01.Value = Format(Me.DB01.Value, "00000000000")
ElseIf Len(Application.WorksheetFunction.Substitute(Me.DB01.Value, " ", "")) = 10 Then
    Me.DB01.Value = Format(Me.DB01.Value, "0000000000")
ElseIf Len(Application.WorksheetFunction.Substitute(Me.DB01.Value, " ", "")) = 9 Then
    Me.DB01.Value = Format(Me.DB01.Value, "000000000")
ElseIf Len(Application.WorksheetFunction.Substitute(Me.DB01.Value, " ", "")) = 8 Then
    Me.DB01.Value = Format(Me.DB01.Value, "00000000")
ElseIf Len(Application.WorksheetFunction.Substitute(Me.DB01.Value, " ", "")) = 7 Then
    Me.DB01.Value = Format(Me.DB01.Value, "0000000")
ElseIf Len(Application.WorksheetFunction.Substitute(Me.DB01.Value, " ", "")) = 6 Then
    Me.DB01.Value = Format(Me.DB01.Value, "000000")
Else
    MsgBox "Supply Number Incorrect", vbCritical, "Error"
    Me.DB01.Value = ""
End If

End Sub

[Artik]

Of course, there is no problem. I just wanted to show two different results, so I used an array and a loop.
Try next modification

Sub BBB()
    Dim MyString    As String
    Dim strTmp      As String
    Dim strChar     As String

    MyString = Me.TextBox1.Value

    If Len(MyString) = 21 Then

        If IsNumeric(MyString) Then
            strTmp = Format(MyString, "## ### ### ## #### #### ###")
            Debug.Print "Input: " & MyString
            Debug.Print "Output: " & strTmp
        Else
            strChar = Mid(MyString, 7, 1)
            If strChar Like "[A-z]" Then
                strTmp = Replace(MyString, strChar, 7)
                strTmp = Format(strTmp, "## ### ### ## #### #### ## #")
                Mid(strTmp, 9, 1) = strChar
                Debug.Print "Input: " & MyString
                Debug.Print "Output: " & strTmp
            End If
        End If

    Else
        MsgBox "Invalid input value", vbExclamation
    End If

End Sub

Artik

[rakotonirinas]

Sorry, me again. I also thought to make things easier, instead of grouping them similar to the full 21 numeric one, then I can create only 2 groups if there is at least one letter in the 21 characters string [the letter will be always in either the 6th, 7th or 8th character], i.e., #####X## #############.
This time it will only be 2 groups but I still need to check whether the string has a letter in it or not and not sure how to do it, I guess.
Thank you.

[Artik]

If there are no letters in the tested string, the IsNumeric function will return True. This is probably the simplest test to check for the presence of letter(s).

Artik

[rakotonirinas]

I tried to combine both @Artik and @MrShorty suggestions with mine and it comes back with an error [compile error: Else without If]and yet the if, I believe, is well defined in the vba.
Is there anything I do incorrectly here? Thank you

Private Sub DB01_AfterUpdate()

Dim MyString    As String
Dim strTmp      As String
Dim strChar     As String
Dim i           As Long
Dim textarray(0 To 6)    As String
Dim outputtxt   As String

MyString = Me.DB01.Value

If Len(MyString) = 21 Then
   
    If IsNumeric(MyString) Then
            strTmp = Format(MyString, "00 000 000 00 0000 0000 000")
            Debug.Print "Input: " & MyString
            Debug.Print "Output: " & strTmp
            Me.DB01.Value = strTmp
    Else
            textarray(0) = Left(MyString, 2)
            textarray(1) = Mid(MyString, 3, 3)
            textarray(2) = Mid(MyString, 6, 3)
            textarray(3) = Mid(MyString, 8, 2)
            textarray(4) = Mid(MyString, 12, 4)
            textarray(5) = Mid(MyString, 16, 4)
            textarray(6) = Right(MyString, 3)
            outputtxt = Join(textarray, " ")
            Me.DB01.Value = outputtxt
            End If
    End If

ElseIf Len(MyString) = 13 Then
    Me.DB01.Value = Format(Me.DB01.Value, "00 0000 0000 000")
ElseIf Len(MyString) = 11 Then
    Me.DB01.Value = Format(Me.DB01.Value, "00000000000")
ElseIf Len(MyString) = 10 Then
    Me.DB01.Value = Format(Me.DB01.Value, "0000000000")
ElseIf Len(MyString) = 9 Then
    Me.DB01.Value = Format(Me.DB01.Value, "000000000")
ElseIf Len(MyString) = 8 Then
    Me.DB01.Value = Format(Me.DB01.Value, "00000000")
ElseIf Len(MyString) = 7 Then
    Me.DB01.Value = Format(Me.DB01.Value, "0000000")
ElseIf Len(MyString) = 6 Then
    Me.DB01.Value = Format(Me.DB01.Value, "000000")
Else
    MsgBox "Supply Number Incorrect", vbCritical, "Error"
    Me.DB01.Value = ""
End If

End Sub

[MrShorty]

You've marked the thread as solved, so I don't know if your most recent post is unresolved. Short answer, I count 3 End If statements and only 2 If statements, so you have some kind of mismatch between these. I suspect that one of the middle End If statements is not supposed to be there, but you would need to look at what you intended for this block if and decide where the End If statements are supposed to be.

[rakotonirinas]

Hello. Yes, you are correct @MrShorty and I managed to resolve it last night, hence the reason I updated to "solve". I also included @Artik's suggestion in the subroutine and it worked like a charm. Thank you very much for your help. Much appreciated.