473,325 Members | 2,712 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Separating a String into Multiple Strings with VBA

11
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.
Sep 3 '14 #1
8 1316
NeoPa
32,556 Expert Mod 16PB
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.
Sep 4 '14 #2
Ashabel
11
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.

Expand|Select|Wrap|Line Numbers
  1.             strBin = rstBin!Bin
  2.             strAisle = ""
  3.             strRack = ""
  4.             strLevel = ""
  5.             intAisleLength = 0
  6.             intRackLength = 0
  7.  
  8.             '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.
  9.             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.
  10.                 strAisle = strBin
  11.             Else
  12.                 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.
  13.                     strAisle = "0" & Left(rstBin!Bin, 1)
  14.                     intAisleLength = 1
  15.                 Else
  16.                     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,
  17.                         strAisle = Left(rstBin!Bin, 2)
  18.                         intAisleLength = 2
  19.                     Else 'otherwise give me teh first four.
  20.                         strAisle = Left(rstBin!Bin, 4)
  21.                         intAisleLength = 4
  22.                     End If
  23.                 End If
  24.             End If
  25.  
  26.             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
  27.                 If Not IsNumeric(Mid(strBin, intAisleLength + 2, 1)) Then 'If the second character after strAisle is a character then give me both
  28.                     strRack = Mid(strBin, intAisleLength + 1, 2)
  29.                     intRackLength = 2
  30.                 Else 'otherwise just give me one
  31.                     strRack = Mid(strBin, intAisleLength + 1, 1)
  32.                     intRackLength = 1
  33.                 End If
  34.                 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
  35.             End If
  36.  
  37.  
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)
Sep 4 '14 #3
NeoPa
32,556 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. Public Sub SplitBin(ByVal strBin As String _
  2.                   , ByRef strAisle As String _
  3.                   , ByRef strRack As String _
  4.                   , ByRef strLevel As String)
  5.     Dim lngX As Long
  6.  
  7.     If strBin Like "[!0-9]*" Then
  8.         strAisle = strBin
  9.         strRack = ""
  10.         strLevel = ""
  11.         Exit Sub
  12.     End If
  13.     lngX = Val(strBin)
  14.     strAisle = Format(lngX, "00")
  15.     For lngX = 2 To 5
  16.         If Mid(strBin, lngX) Like "[!0-9]*" Then
  17.             strBin = Mid(strBin, lngX)
  18.             Exit For
  19.         End If
  20.     Next lngX
  21.     lngX = IIf(Mid(strBin, 2) Like "[0-9]*", 1, 2)
  22.     strRack = Left(Left(strBin, lngX)
  23.     strLevel = Mid(strBin, lngX + 1, 2)
  24. End Sub
Sep 4 '14 #4
Ashabel
11
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"?
Sep 4 '14 #5
zmbd
5,501 Expert Mod 4TB
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
Sep 4 '14 #6
Ashabel
11
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.
Sep 4 '14 #7
NeoPa
32,556 Expert Mod 16PB
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.
Sep 4 '14 #8
zmbd
5,501 Expert Mod 4TB
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 (^_^)
Sep 4 '14 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: hokiegal99 | last post by:
How do I say: x = string.find(files, 'this', 'that', 'the-other') currently I have to write it like this to make it work: x = string.find(files, 'this') y = string.find(files, 'that') z =...
2
by: Aleksi Kallio | last post by:
I want to say something like this: <xsl:if test="contains($my-string, {'banana', 'apple', 'lemon'})"> Ie. I want to do "contains" against many strings. I don't want to write them all manually...
1
by: Bruno | last post by:
Hello, I don't know well XML and I can't figure out how to solve this problem .. I need to replace some strings inside a text using XSL and inside a for-each statement (the list of strings to...
5
by: Maury Markowitz | last post by:
I have a byte returned from a DLL that contains n c-style strings inside it. Any suggestions on how to easily pull them out into a string? Encoding helps with a single byte array (although easily...
19
by: nitro_punk85 | last post by:
I'm working on a project for my c++ class and I am having trouble comparing one string to two others using the or operator. It looks something like this: if(answer3 == answer1 || answer2) Is...
1
by: cornedbeef007-groups | last post by:
I have built a database for dog pedigrees. I want to be able to display the siblings for a dog on it's pedigree. I am able to find the names in my database, and at present return them from a UDF...
1
by: gwigg | last post by:
Hi, I am trying to match multiple strings per line from a file and extract them into an array. Apparently the first match is assigned $1, how do I know what the last match is ie $last?? I would like...
4
by: Polarism | last post by:
Hello, I am very new to perl and I am having trouble figuring out how to replace multiple strings in a single file. The file has something around 750k instances that need to be replaced with 350...
4
Markus
by: Markus | last post by:
Hi, guys. I'm receiving a dynamic amount of strings and from these strings I need to make a single string containing them all. However, I need to preserve the NULL characters to separate the...
1
by: emily53 | last post by:
Hi, I'm pretty new to coding in C++, and I'm just wondering if its possible to store multiple strings in a vector, kind of like an array?
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.