How to split out multiple account codes and descriptions from a text string

[ExcelJester]

Dear MrExcel Community

I have often found the advice and guidance I need for past problems on the forum, but I am afraid that today I am at a loss.

I have bunch of financial system accounts (including codes and descriptions) which were printed from the system. I would like to split the codes and descriptions out into separate cells if possible. The problem I am having is that there does not seem to be any clear logic in the way the codes and descriptions are created.

Some codes are all numeric, while others are alphanumeric. Some codes are 4 characters long while others are 5 or 6 characters long. Some descriptions are single words, while others contain multiple words. Some descriptions contain numeric values as well. Some of the strings contain only three or four account codes and descriptions, while other strings contain five codes and descriptions.

The only logic I can see is that each code is followed by its description, and that the are definitely spaces between each code and description. The maximum number of codes and descriptions is 5.

I have been working with multiple sets of IFs, FINDs, MATCHs, MIDs, etc. but my formulae are starting to get out of hand, and I thought it might be time to ask for help. I am not sure if it is possible due to the lack of apparent logic in the naming conventions, but I know that I have seen some pretty amazing (yet simple and clean) VBA codes which can work wonders with seemingly impossible tasks in Excel. I hope someone is able to help!

I have tried to reproduce an example of the strings printed from the system below:

Strings Given (assume in column A)
1234 Apples 0000 Bananas 123 Peaches
2345 Apples Pears 0000 Bananas Oranges 345 Peaches 0000 Apples
2345 Pears 00000 3 Pears A23 Peaches 0000 2 Apples 000000 Nuts
3456 Pears23 000000 6% Oranges 0000 Peaches 0000 Apples 21% A000000 5 Nuts
4567 Apples 6% 000 Bananas Nuts 0000 Peaches
45678 Apples 21% A000 Bananas 0000 Peaches 5
A123 13 Apples B0000 Apples 0000 Peaches Apples

Here are the codes and descriptions that I would like to obtain as a reference (assume in columns B - K)
For row 1, the solution would be (note codes and descriptions 4 and 5 may be blank):
[TABLE="width: 178"]
<tbody>[TR]
[TD]Code 1[/TD]
[TD]1234[/TD]
[/TR]
[TR]
[TD]Description 1[/TD]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Code 2[/TD]
[TD]0000[/TD]
[/TR]
[TR]
[TD]Description 2[/TD]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]Code 3[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]Description 3[/TD]
[TD]Peaches[/TD]
[/TR]
[TR]
[TD]Code 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Description 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Code 5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Description 5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

For row 3, the solution would be:
[TABLE="width: 112"]
<tbody>[TR]
[TD][TABLE="width: 178"]
<tbody>[TR]
[TD]Code 1[/TD]
[TD]2345[/TD]
[/TR]
[TR]
[TD]Description 1[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]Code 2[/TD]
[TD]00000[/TD]
[/TR]
[TR]
[TD]Description 2[/TD]
[TD]3 Pears[/TD]
[/TR]
[TR]
[TD]Code 3[/TD]
[TD]A23[/TD]
[/TR]
[TR]
[TD]Description 3[/TD]
[TD]Peaches[/TD]
[/TR]
[TR]
[TD]Code 4[/TD]
[TD]0000[/TD]
[/TR]
[TR]
[TD]Description 4[/TD]
[TD]2 Apples[/TD]
[/TR]
[TR]
[TD]Code 5[/TD]
[TD]000000[/TD]
[/TR]
[TR]
[TD]Description 5[/TD]
[TD]Nuts[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


For row 4, the solution would be:
[TABLE="width: 236"]
<tbody>[TR]
[TD]Code 1[/TD]
[TD]3456[/TD]
[/TR]
[TR]
[TD]Description 1[/TD]
[TD]Pears23[/TD]
[/TR]
[TR]
[TD]Code 2[/TD]
[TD]000000[/TD]
[/TR]
[TR]
[TD]Description 2[/TD]
[TD]6% Oranges[/TD]
[/TR]
[TR]
[TD]Code 3[/TD]
[TD]0000[/TD]
[/TR]
[TR]
[TD]Description 3[/TD]
[TD]Peaches[/TD]
[/TR]
[TR]
[TD]Code 4[/TD]
[TD]0000[/TD]
[/TR]
[TR]
[TD]Description 4[/TD]
[TD]Apples 21%[/TD]
[/TR]
[TR]
[TD]Code 5[/TD]
[TD]A000000[/TD]
[/TR]
[TR]
[TD]Description 5[/TD]
[TD]5 Nuts[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for taking the time to look at this! I look forward to hearing if there are any possible solutions that would require little manual intervention!

 

[Rijnsent]

Hi Jester,
I gave your puzzle a try, but the code won't get anywhere near "simple and clean". The logic I get from your dummy data:
-in every row there is code, description, code, description, etc all items are seperated by a space (let's call every space separated item an "element")
-codes are always 1 element, no spaces, descriptions can be 2 elements and have a space in between
-when are 2 elments together 1 description?
--if an element is 2 characters or less it belongs to the description, which means merging it with the field before or after it
--if an element is 3 characters and contains a %, it also belongs to the description and should be merged.

The function below can be put in a workbook and called from a worksheet, add a top row with the element number (1 to 10) and put the formula in B2:K8 (with your example data in column A). B2 = =MySplitFunction($A2;B$1)

Hope that helps,

Koen

Public Function MySplitFunction(InputStr As String, ItmNr As Integer) As String

    Dim tmpArr As Variant
    Dim resArr As Variant
    
    tmpArr = Split(InputStr, " ")
    ReDim resArr(0 To UBound(tmpArr))
    itm = "code"
    j = 0
    
    For i = 0 To UBound(tmpArr)
        If itm = "code" Then
            resArr(j) = tmpArr(i)
            itm = "descr"
            j = j + 1
        Else
            If i = UBound(tmpArr) Then
                resArr(j) = tmpArr(i)
            Else
                If Len(tmpArr(i)) <= 2 Or Len(tmpArr(i + 1)) <= 2 Or (Len(tmpArr(i)) = 3 And InStr(tmpArr(i), "%") > 0) Or (Len(tmpArr(i + 1)) = 3 And InStr(tmpArr(i + 1), "%") > 0) Then
                    resArr(j) = tmpArr(i) & " " & tmpArr(i + 1)
                    i = i + 1
                Else
                    resArr(j) = tmpArr(i)
                End If
            End If
            j = j + 1
            itm = "code"
        End If
    Next i
    
    If ItmNr - 1 > UBound(tmpArr) Then
        MySplitFunction = ""
    Else
        MySplitFunction = resArr(ItmNr - 1)
    End If
    
End Function