VBA, just like any other language gives you a set of functions to perform various operations on strings. And VBA Split is one of those string functions. Microsoft introduced Split function with VBA Version 6 (in Office 2000).As the name suggests, the job of Split statement is to break, split or divide a string based on a particular criteria.Split is one of the many Substring functions that can be used in VBA. I have written a whole post on these Substring functions, you can read it.Definition and Syntax of VBA Split Function:Split can be defined as a function that can split a text string into an array, by making use of a delimiter character. It returns a zero-based, one-dimensional array holding the parts of the original text string.The Syntax of VBA Split Statement is as follows:Split ( textstring, delimiter, limit, compare)Here, ‘ textstring’ refers to the string that you need to break.‘ delimiter’ refers to the character which divides the string into parts. This is an optional argument, space character “ ” is treated as the default delimiter.‘ limit’ is an optional parameter.
It specifies the maximum number of parts into which the input string should be divided. The default value is -1, which means that the string should be divided at each occurrence of ‘ delimiter’.‘ compare’ is also an optional argument that specifies the comparison method to be used while evaluating the strings. ‘ compare’ argument can have anyone of the below values: ConstantValueDescriptionCompareMethod.Binary0Performs a binary comparison i.e.
Change font size/spacing of existing text with VBA grogo 4 months ago I have some text that I need to change the size and line spacing to using VBA but I don't know how. Use the Chr and Asc functions to convert between the numerical and string representations of VBA's character code set: 2. Use the Asc function to convert a character to its numerical representation.
Explanation:In this code, we have used a Split function to break the string into parts. Later, in the code we have used a to iterate the array returned by the Split Function in order to show the array contents using a.Example 3: Use the above example, but here after splitting the string we just need to display its third part.
![]()
To make it clearer, let’s say if I divide the string (“Welcome to Excel Trick”), then in this case I just need the output as “Excel” i.e. The third word in the string.So, the code would be: Sub BreakStringDim WrdString As StringDim textstring As Stringtextstring = 'Welcome to Excel Trick'WrdString = Split(textstring)(2)MsgBox 'The third part is: ' & WrdStringEnd SubExplanation:In this code, you would have noticed that instead of using a String Array for accepting the return from the Split Function I have taken a string variable. This is because by writing the Split Statement as: WrdString = Split(textstring)(2), we are telling it to return only the third element of the array (which is a string) and not the complete array.Example 4: Now use the same code of Example 2 just change it to demonstrate the use of limit argument.
Sub BreakStringDim WrdArray As StringDim textstring As Stringtextstring = 'Welcome to Excel Trick'WrdArray = Split(textstring, 3)For i = LBound(WrdArray) To UBound(WrdArray)strg = strg & vbNewLine & 'Part No. ' & i & ' - ' & WrdArray(i)Next iMsgBox strgEnd SubExplanation:Above code is very similar to the one that we have seen in Example 2.
Comments are closed.
|
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |