1 / 12

Working with Strings

Working with Strings. String Structure. A string in VBA can be variable length and has an internal structure Each character in the string has a position number, called an index The first character has index 1, etc. So the string “This is a string.” has 17 characters, counting the blanks:.

raven
Download Presentation

Working with Strings

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Working with Strings

  2. String Structure • A string in VBA can be variable length and has an internal structure • Each character in the string has a position number, called an index • The first character has index 1, etc. So the string “This is a string.” has 17 characters, counting the blanks:

  3. Manipulating Strings • VBA has a number of string functions that you can use to manipulate strings • A list of them can be found in the Formulas tab by clicking the Text icon (Windows) or checking under the References icon (Mac) • We’ll show how to use a few of the most useful ones by doing an example • In the ParseNames workbook, the macro called “parse” splits each entry in the first column into separate words, using the blanks to identify word breaks, and puts each word in its own column

  4. We’ll discuss this bit by bit… SubParse() Const BLANK As String = " " DiminString, aWordAs String DimblankPosition, j, k As Integer j = 1 Do While Not IsEmpty(Cells(j, 1).Value) inString = Cells(j, 1).Value k = 2 'While the string is not empty, peel off a word and put it in the text box Do While Len(inString) > 0 blankPosition = InStr(inString, BLANK) ' VBA indexes strings starting at 1 IfblankPosition > 0 Then 'get the characters up to the blank aWord = Left(inString, blankPosition - 1) 'get the rest of the string starting at the blank, then trim it inString = Trim(Right(inString, Len(inString) - blankPosition)) Else 'this happens on the last word in the string aWord = inString inString = "" End If Cells(j, k).Value = aWord k = k + 1 Loop j = j + 1 Loop End Sub

  5. Variables and Constants • We use a constant BLANK set to “ “. The idea is to make the code easier to read • Variable j is always the row we are working on • Variable k is the column we are going to put our next item in. It starts at 2 because we don’t want to erase what’s in column 1 • inString is the string we’re working on. It is set at the beginning of the main loop: inString = Cells(j, 1).Value

  6. Outer Loop Structure • This code starts with line 1 and keeps going till it finds an empty first column, using a Do While loop. Variable j is the current line number. j = 1 Do While Not IsEmpty(Cells(j, 1).Value) <body of outer loop: process one line> j = j + 1 Loop

  7. The inner loop • The idea is to keep peeling words off our string until it’s empty. Each time, we put a word in a new column. So the structure of the inner loop is: k = 2 Do While Len(inString) > 0 <body of inner loop: find a word, remove it from inString, put it in column k> k = k + 1 Loop

  8. The InStr Function • Use InStr to find the first occurrence of a string A inside another string B • String A can be any string of any length. In our example, it will be BLANK, the string consisting of one blank blankPosition = InStr(inString, BLANK)

  9. The Left, Right, and Trim functions • Left returns the part of a string up to the position indicated, starting at the left end aWord = Left(inString, blankPosition - 1) • Right returns a specified number of characters from the right end of the string. • Trim returns a string with leading and trailing blanks removed and with multiple inner blanks replaced with single blanks (it can have problems with Unicode; see the help article)

  10. Consider an Example blankPosition = InStr(inString, BLANK) ‘this will be 6 aWord = Left(inString, blankPosition - 1) ‘ aWord is now James, characters 1-5

  11. The Function Len • Len returns the length of a string. In our example, we need it to figure out how many characters to get with the Right function. We want all the characters after the blank we found: Right(inString, Len(inString) - blankPosition) 12 - 6 is 6, which is the number of characters we want

  12. Using these functions • You can do a lot of clever manipulation of strings with these and the other string functions • Writing this kind of code is rather fiddly, meaning you have to tinker around and look at some examples to get the formulas just right • It can be helpful to Trim strings before you do anything else with them: inString = Trim(inString)

More Related