String Manipulation
Much like the title phrase implies, this is something like playing cat's cradle--you take a simple string and do all sorts of things to it, and the end result doesn't necessarily look much like the simple loop of twine you began with.
In order to have a static example for comparisons between the various commands below, I'll set a$ = " Library 3 " (yes, the spaces are intentional).
If you want to know how many characters are in a string, then you want to use the Len statement.
x = Len(a$)
In this example, x = 11. Later on you will see some examples of a neat trick you can do with Len.
If you want to convert a string so that all the characters are capitals, use UCase.
b$ = UCase(a$)
In this example, b$ = " LIBRARY 3 ". Note that the digit didn't change, nor did the spaces. UCase is often used when comparing something typed by the user to some other string. Due to the way the computer works, "az" does not equal "AZ"! Thus, if you want "azzzz", "Azzzz", "AzZzZ" and "AZZZZ" to all come up as matches, it's easiest to use UCase when comparing the strings.
If you want to get rid of any leading and trailing spaces in a string, you need to trim the string.
b$ = Trim(a$) c$ = LTrim(a$) d$ = RTrim(a$) e$ = Trim(b$)
Trim removes all leading and trailing spaces, thus b$ = "Library 3"; LTrim removes only spaces on the left end of the string, thus c$ = "Library 3 "; RTrim removes all spaces on the right end of the string, thus d$ = " Library 3"; trimming a string with no spaces on the ends has no effect, thus e$ = b$ = "Library 3".
If you want to change a string so that you're left with only part of it, there are three commands you should learn.
b$ = Left(a$, 4) c$ = Mid(a$, 2, 5) d$ = Right(a$, 4)
Left and Right are fairly self-explanatory; the number is the number of characters to take--thus, b$ = " Lib" and d$ = "y 3 ". Mid takes characters from the middle of a string; the first character is the place to start, and the second is the length to take--thus, c$ = "Libra". If one of these commands is given a length that is longer than the length of the string, the entire string is taken.
b$ = Left(a$, 15)
In this case, b$ = a$; nothing more, nothing less.
Now for that trick I mentioned earlier. If you want to take a chunk from the left side of a string, but you don't know how long it needs to be--only that you need everything except the last 2 characters, you can combine Left & Len to do the job. I'll throw in a LTrim for good measure, so you can see how these commands can be nested inside each other.
b$ = Trim(Left(a$, Len(a$)-2))
(Watch those parentheses! For every left parenthesis, there must be a right one or the macro won't run.) What the above statement does is take the length of a$ (11); then subtract 2 from that number (9); then use the resulting number as the number of characters to take from the left side of a$ (" Library "); then remove all spaces from either side of a$. The end result is b$ = "Library". When you try something like this, you have to remember that commands resolve from the inside out, and that modifiers (like the -2 in the above example) need to be properly placed. Examples of what not to do if you want to take "a$" and end up with "Library":
b$ = Trim(Left(a$, Len(a$-2))) c$ = Left(Trim(a$), Len(a$)-2))
In the first case, the macro won't run properly because you can't subtract 2 from a string. In the second case, because the Trim is executed before the Left, what occurs is that the length of a$ is measured and a$ is trimmed ("Library 3"), then the leftmost 9 characters are stored in c$--which is now the entire string, giving you an end result of "Library 3".
Unlike Left and Right, Mid has two versions: the function described above, and the statement version. The statement version, rather than copying a section of one string into another as above, replaces part of the string with a new segment. The new segment and the segment being replaced must be the same length or else errors will result.
b$ = a$ Mid(b$, 2, 7) = "Outreach"
The result of this statement is that b$, which started out being the same as a$, now is " Outreach 3 ". Combined with other commands, it's possible to find and replace various segments of a string.
If you want to know if a character or a string is contained in another string, the command to know is InStr. The result of the statement is the position of the first character of the one string in the other; unlike some commands, the position count begins with 1. If the search string occurs multiple times, only the first is found.
x = InStr(a$, "Libra") y = InStr(6, a$, "r") z = InStr(1, a$, "L", 1) aa = InStr (a$, "cat")
The first statement is a simple search starting with the first character in a$ and going until the search term is found; x = 2. The second statement tells the computer to start searching with the sixth character in a$, but this does not affect the end result, which still counts from the beginning of the string; y = 7. Note that, due to the specified starting place, the command could not find the first "r" and instead found the second. The third statement tells the computer to start at the beginning of the string (this can be specified, even though it's the default), and also that the search should be case-sensitive (0 in the last position means case doesn't matter); z = 2. The last statement involves a search that won't be found; the result of that is aa = 0. If you want to check for multiple occurrences of one string in another, the way to do this is by using multiple InStr commands (or looping back through the same one; more on looping in a later lesson), changing the start position each time to the position of the last find +1.
If you want to work with the ASCII value of a single-character string or the first character of a listed string, the command to do that is Asc.
x = Asc("L")
y = Asc(Trim(a$))
In this case, x = y = 76. If a$ wasn't trimmed first, y would equal 32,
the ASCII character for a space.
Converting a string into a number might be difficult, since the computer only recognizes the ASCII code of numbers rather than the "normal" values. However, the Val command does exactly that.
x = Val(a$) y = Val(a$ + "2") z = Val(RTrim(a$) + "2")
Val runs through the string until it finds a number, then continued until it finds the first non-number. It then converts the resulting string into a numeric variable. In the first case above, x = 3. In the second, there is a space in the "3" in a$ and the "2" that is tacked on; y = 3. In the third case, the space is trimmed off before the "2" is added, so z = 32.
Next time, normalized data...