Unusual string

[Yap]

I have a an usual string that needs to be splited. Here's a sample:
XT225.00AJ516.00YQ45.11EU26.88VJ143.62US23.65YC 30.10XY21.50XA10.75AY

It should split to this:
XT 225.00 AJ 516.00 YQ 45.11 EU 26.88 VJ 143.62 US 23.65 YC 30.10 XY 21.50 XA 10.75 AY

Any ideas?

[Rod]

Is the result also one long string or do you need separate strings?

One method - maybe the only way - is to read each character starting from the first and identify numeric to alphabetic (and vice versa) changes, inserting a space at these points. The decimal points complicate things a little.

I'll play with some code and get back to you.

[Yap]

Each as seperate string.

I was also thinking about reading each chr since the only way to detect the change is when it switches from text to number or space. Did you notice a space between YC and 30? The space is not static at that location, it can appear anywhere in the string.

[Rod]

Here you go.

Public Function ConvertString(rstrString As String) As String

    Dim i As Integer
    
    rstrString = Trim(rstrString)
    If Len(rstrString) <= 1 Then ConvertString = rstrString: Exit Function
    
    ConvertString = ""
    For i = 1 To Len(rstrString)
        ConvertString = ConvertString & Mid(rstrString, i, 1)
        If i < Len(rstrString) Then
            If Mid(rstrString, i, 1) Like "[a-z,A-Z]" And Mid(rstrString, i + 1, 1) Like "[0-9]" Then
                ConvertString = ConvertString & " "
            ElseIf Mid(rstrString, i, 1) Like "[0-9]" And Mid(rstrString, i + 1, 1) Like "[a-z,A-Z]" Then
                ConvertString = ConvertString & " "
            End If
        End If
    Next
    
End Function

P.S. Just seen your second post. No, I didn't see the space and my published solution returns one string. However this can easily be adapted to split into an array. I'll modify my code.

[June7]

Do you want to extract these values for saving to separate fields in a table?

The alpha parts are always 2 characters?

The first alphanumeric pair is easy.

Left([stringfield],2)

Format(Val(Mid([stringfield],3)),"0.00")

After that, have to whittle away the parts. Take the length of the first pair and use that to extract remainder of the string. Repeat. Until Rod comes back, consider:

Sub BreakString()
Dim x As String, y As String, z As String, S As String
x = Replace("XT225.00AJ516.00YQ45.11EU26.88VJ143.62US23.65YC 30.10XY21.50XA10.75AY", " ", "")
Do While InStr(x, ".") > 0
    y = Left(x, 2)
    z = Format(Val(Mid(x, 3)), "0.00")
    Debug.Print y & " " & z
    x = Mid(x, Len(y & z) + 1)
    S = S & y & " " & z & " "
Loop
Debug.Print Right(x, 2)
S = S & Right(x, 2)
Debug.Print S
End Sub

EDIT: Rod already back. As you can see, more than one way ...

[Rod]

Modified:

Public Function ConvertString(rstrString As String) As Variant
    Dim i As Integer
    Dim strWork As String
    
    rstrString = Trim(rstrString)
    rstrString = Replace(rstrString, " ", "")
    
    
    If Len(rstrString) <= 1 Then strWork = rstrString: GoTo Exit_Procedure
    
    strWork = ""
    For i = 1 To Len(rstrString)
        strWork = strWork & Mid(rstrString, i, 1)
        If i < Len(rstrString) Then
            If Mid(rstrString, i, 1) Like "[a-z,A-Z]" And Mid(rstrString, i + 1, 1) Like "[0-9]" Then
                strWork = strWork & ":"
            ElseIf Mid(rstrString, i, 1) Like "[0-9]" And Mid(rstrString, i + 1, 1) Like "[a-z,A-Z]" Then
                strWork = strWork & ":"
            End If
        End If
    Next
    
Exit_Procedure:
    ConvertString = Split(strWork, ":")
    Exit Function
    
End Function

Use this call to test:

    Dim var As Variant
    Dim i As Integer
    var = ConvertString("XT225.00AJ516.00YQ45.11EU26.88VJ143.62US23.65YC 30.10XY21.50XA10.75AY")
    If IsArray(var) Then
        MsgBox UBound(var)
        For i = 0 To UBound(var)
            MsgBox var(i)
        Next
    End If

P.S. Hi June - as I often say, 'Give ten programmers a problem and you will end up with eleven solutions.'

[Yap]

Yes, the alpha part is alway 2 letters. The extracted values are to be saved to separate fields in a table. I'll give it a try.

Thanks.