Here's Step 2 of the wizard that's used to split a single column of delimited data into multiple columns.
In this case, three delimiters are specified: tab, comma, and colon. The problem is, Excel tries to be helpful by remembering these settings for subsequent CSV imports and paste operations.
These descriptions appear in Function Arguments dialog box -- which is displayed after you choose a function using the Insert Function dialog box. You might think that this function is just a variation on Excel's FIND function or VBA's Instr function. The Exact Word In String function looks for a complete word -- not text that might be part of a different word.
Here's a simple (but very useful) user-defined function: Sub Describe Function() Dim Func Name As String Dim Func Desc As String Dim Category As String Dim Arg Desc(1 To 3) As String Func Name = "EXTRACTELEMENT" Func Desc = "Returns the nth element of a string that uses a separator character" Category = 7 Arg Desc(1) = "String that contains the elements" Arg Desc(2) = "Element number to return" Arg Desc(3) = "Single-character element separator" Application. The examples in the accompanying figure should clarify how this function works.
The solution is to "fake" a text-to-columns operation.
Macro Options _ Macro:=Func Name, _ Description:=Func Desc, _ Category:=Category, _ Argument Descriptions:=Arg Desc End Sub You need to run this macro only one time. Cell C2 contains this formula, which was copied to the cells below: The function identifies the complete word trapped, but not the word trap, which is part of trapped.
After doing so, the descriptive information is stored in the workbook (or add-in) that defines the function. Also, note that a space is not required after a word in order to identify it as a word.
Before ending, the procedure deletes the temporary string.
Category: Formulas | [Item URL] A companion file is available: Click here to download Many events are scheduled for a particular occurrence of the day within a month.