Separating a String into Multiple Strings with VBA

[Ashabel]

I have a string (strBin) which needs to be seperated out into 3 distinct parts (strAisle, strRack, strLevel).

- if the first character is not a number

• strAisle = strBin
• strRack = ""
• strLevel = ""

strAisle must follow the following quidelines:
- If the first and second characters are numbers, let me have the first 2 characters. (if only the first character is a number then add a zero in front, then give me the first 2 characters)
- If the first 2 characters = "90" then I need the first 4 characters.


strRack must follow the following guidelines:
- strRack must be 1-2 characters long starting immidiately after strAisle and is 2 characters if and only if the second character after strAisle is not a number.

strLevel must follow the following guidelines:
- the next 2 characters immidiately following strAisle and strRack

Any characters remaining after strLevel are to be ignored.

[NeoPa]

Please check the accuracy of your question. Assuming strRack & strLevel are actually String variables then they cannot contain Null values. Also the logic for strRack looks as if it may be reversed.

When you are very clear as to exactly what you want then please provide some examples and indicate the intended results in the three variables. This will make it clear that we understand your (quite complicated) requirements in the same way that you do, and we can proceed to offer some assistance.

[Ashabel]

Neo,
You are correct. I had assumed incorrectly that strings could be null rather than "" (empty).

The sample data that I have is as follows:

Bin: "01C02"
Aisle: "01"
Rack: "C"
Level: "02"

Bin: "2C01"
Aisle: "02"
Rack: "C"
Level: "01"

Bin: "FABB"
Aisle: "FABB"
Rack: ""
Level: ""

Bin: "80B03A02"
Aisle: "80"
Rack: "B"
Level: "03"

Bin: "9001E01"
Aisle: "9001"
Rack: "E"
Level: "01"

Bin: "9007AA02"
Aisle: "9007"
Rack: "AA"
Level: "02"

Below is the code that I had created to answer this... The more I typed out the rules required for each part the more clear the solution in my mind became.

            strBin = rstBin!Bin
            strAisle = ""
            strRack = ""
            strLevel = ""
            intAisleLength = 0
            intRackLength = 0
            
            'This will test for Aisle and set the Aisle variables. I have hardcoded the "aisle length" rather then using a Len(strAisle) everywheres so that I can control what all is going on..... also for the case of a single number at the start, I need to control the rest of the extraction because the strAisle length will not be equal to the number I require to use for the rest.
            If Not IsNumeric(Left(rstBin!Bin, 1)) Then 'If the firstBin character is a letter then set the aisle as the bin and no further testing needed.
                strAisle = strBin
            Else
                If Not IsNumeric(Left(rstBin!Bin, 2)) Then 'if the second character is not a number then the aisle must be "0" & the firstBin character. This is where the aisle length will not equal the amount of characters I need to proceed with.
                    strAisle = "0" & Left(rstBin!Bin, 1)
                    intAisleLength = 1
                Else
                    If Left(rstBin!Bin, 2) <> "90" Then 'if the first 2 characters do not start start with "90" than I only need the first 2,
                        strAisle = Left(rstBin!Bin, 2)
                        intAisleLength = 2
                    Else 'otherwise give me teh first four.
                        strAisle = Left(rstBin!Bin, 4)
                        intAisleLength = 4
                    End If
                End If
            End If
            
            If intAisleLength > 0 Then 'if the Aisle length has been set to anything at all (a number was found for the firstBin x spaces) proceed with the other comparisons
                If Not IsNumeric(Mid(strBin, intAisleLength + 2, 1)) Then 'If the second character after strAisle is a character then give me both
                    strRack = Mid(strBin, intAisleLength + 1, 2)
                    intRackLength = 2
                Else 'otherwise just give me one
                    strRack = Mid(strBin, intAisleLength + 1, 1)
                    intRackLength = 1
                End If
                strLevel = Mid(strBin, intAisleLength + intRackLength + 1, 2) 'Give me the next 2 characters following both the strAisle and strRack. Ignore all remaining characters for that
            End If

I do know that while IsNumeric is not perfect, I only require it to test 1-2 characters at a time so for now it should suffice until I design a better function (or google for a better one that's published already)

[NeoPa]

Nice work Ashabel :-)

Sorry. Had to reset Best Answer as that can only be awarded to the OP (You) in rare cases. I'm glad that you noticed that preparing the question to include more details has the side-effect of helping you better understand it yourself. That is often the way in truth.

I also noticed, from your examples, that your original explanation was detailed and accurate. Not an easy set of logic to put down but you seem to have done it well. Good for you. I will assume, again from the examples, that whenever strBin starts with a number then the format of the string is always {A-Some digits}{B-Some other characters}{C-More digits}{D-Irrelevant} and that we want to end up with strAisle=A, strRack=B & strLevel=C. D is always ignored.

Anyway, here's how I'd do it :

Public Sub SplitBin(ByVal strBin As String _
                  , ByRef strAisle As String _
                  , ByRef strRack As String _
                  , ByRef strLevel As String)
    Dim lngX As Long

    If strBin Like "[!0-9]*" Then
        strAisle = strBin
        strRack = ""
        strLevel = ""
        Exit Sub
    End If
    lngX = Val(strBin)
    strAisle = Format(lngX, "00")
    For lngX = 2 To 5
        If Mid(strBin, lngX) Like "[!0-9]*" Then
            strBin = Mid(strBin, lngX)
            Exit For
        End If
    Next lngX
    lngX = IIf(Mid(strBin, 2) Like "[0-9]*", 1, 2)
    strRack = Left(Left(strBin, lngX)
    strLevel = Mid(strBin, lngX + 1, 2)
End Sub

[Ashabel]

Neo,
I wish I had thought up your A,B,C,D analysis as that is exactely what happens when A is numbers (digits).

I do have some questions regarding your code.

Line 7 reads "If strBin Like "[!0-9]*" Then"

What does that ! mark do?

Line 14 reads strAisle = Format(lngX, "00")

When lngX = 9001, this remains unaffected by the format?

Line 18 reads "Exit For"

That's just a "force the loop to terminate"?

[zmbd]

1) the Bang (!) means "not"
[!0-9] if not in list of values from 0 thru 9 then...

2) the format only ensures that a single number "#" shows as double digit "0#" where # is a whole number
9001 is a four digit number so the format has no effect.

3) yes

[Ashabel]

Thank you both. I will certainly try to implement this and think on it further to see if I can improve any other bits of code I'm using.

[NeoPa]

My answers are very similar to Z's (but I'm a fussy old bugger so I'll post them anyway :-D)

1. [!...] matches any single character which is not included in the list (...).
2. The format string "00" ensures that a number is displayed as an integer and that at least two digits are shown.
   It's not entirely accurate to say it has no effect, yet in this case the result is equivalent to printing the number except without any spaces.
3. Yes. That's exactly what it does.
   Other flavours include Exit Do - Exit Sub - Exit Function - etc. for the various different things to exit from.

Ashabel:
I wish I had thought up your A,B,C,D analysis as that is exactely what happens when A is numbers (digits).

I'm gratified. I'm also very experienced with such situations and appreciate the extreme importance of relating the logic as clearly and understandably as possible. For you this is simply a step on the path to picking up more experience. From your reactions I would guess you're a good learner and will benefit from all the ideas shared here.

[zmbd]

NeoPa's answer is the "more correct" answer.

and being a "fussy ole bugger" isn't a bad thing...
but only when there's food and drink involved (^_^)