|Posted: July 24, 2006, 5:19 pm - IP Logged|
Someone asked about this recently.
Thought I would share it with all.
I found this Code searching the Web.
Thought it would be a good learning tool for those interested in VBA (Visual Basic for Applications) and using User Defined Functions (UDF)
How to Create Excel User Defined Functions
- Open up a new workbook.
- Get into VBA (Press Alt+F11)
- Insert a new module (Insert > Module)
- Copy and Paste the following code in the right box under “Option Explicit”
Function GetElement(text As Variant, n As Integer, _
delimiter As String) As String
'Returns the nth element from a delimited text string
Dim txt, str As String
Dim count, i As Integer
'Manipulate a copy of the text string
txt = text
'If a space is used as the delimiter, remove extra spaces
If delimiter = Chr(32) Then txt = Application.Trim(txt)
'Add a delimiter to the end of the string
If Right(txt, Len(txt)) <> delimiter Then
txt = txt & delimiter
'Initialize count and element
count = 0
str = ""
'Get each element
For i = 1 To Len(txt)
If Mid(txt, i, 1) = delimiter Then
count = count + 1
If count = n Then
GetElement = str
str = ""
str = str & Mid(txt, i, 1)
GetElement = ""
- Get out of VBA (Visual Basic for Applications) (Press Alt+Q)
Uses for this are the following:
Say for example you copy and pasted a Pick 6 drawing from your official lottery Website. The winning drawing was 01-02-03-04-05-06. (Fat chance this every coming out). You want to get rid of all the “-“ dashes and place the individual numbers in 6 separate cells.
Here’s an example:
In cell A1 you copy and pasted the winning drawing: 01-02-03-04-05-06
Click cell B1
Click "User Defined" in the “Function Category”
On the right under “Function Name”: look for “GetElement”
Highlight “GetElement” and select OK.
In cell B1 enter the following formula: =GetElement($A1,1,"-")
In cell C1 enter the following formula: =GetElement($A1,2,"-")
In cell D1 enter the following formula: =GetElement($A1,3,"-")
In cell E1 enter the following formula: =GetElement($A1,4,"-")
In cell F1 enter the following formula: =GetElement($A1,5,"-")
In cell G1 enter the following formula: =GetElement($A1,6,"-")
You can change the “-“ to “,” or whatever separates the numbers in cell A1.
You can also use this for Pick 3, Pick 4, Cash 5.