To do the above example using Mid , you would use the following code:
Mid(ActiveCell,8). This returns the remaining portion of the string after starting at the 8 th position. If the ActiveCell contains: “ID50056Chopsticks”, then this code returns “Chopsticks”. Mid can also be used to just take out the middle of a string. For example, if we only want the numeric portion of the ID, we could use the following code:
Mid(ActiveCell,3,5).This code returns “50056” – i.e. the 5 characters starting with the 3rd character in the string “ID50056Chopsticks”.
Split is a way to put parts of the string into an array for later use. An array stores a series of objects. In this case, the objects are strings. Let’s say we want to store the first and last name in an array. If the string was “Simpson, Marge”, we could use the following code to store the two parts of the string in an array called "nameArray":
nameArray=Split(ActiveCell,", "). In this case, the following code would return “Marge”:
nameArray(1). The array starts with the 0th position, so nameArray(0) would return “Simpson”. Split uses a delimiter to split the string. In this example, I have used a comma followed by a space to split the string. The code then removes the delimiter from the string, stores the value preceding it in the 0th position of the array, and then searches for another instance of the delimiter. If no more instances of it exist, then it places the remaining portion of the string in the last position of the array.
Let’s try a more complicated example. Let’s use a string of zip codes separated by commas: “90210,24998,90265,44117,84042”. If we split this using the comma as a delimiter, we will end up with an array of five zip codes. This code will store it in an array called "zipArray":
zipArray=Split(ActiveCell,","), and then this code will return one of its values:
zipArray(3) – in this case, “44117”. Remember, the array starts in the 0th position, so asking for the one in the 3rd position will return the 4th value.