Results 1 to 5 of 5

Thread: VBA Versions of my "Get Field" and "Get Reverse Field" formulas

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    VBA Versions of my "Get Field" and "Get Reverse Field" formulas

    While it is more efficient to use the formulas posted in these two articles when parsing delimited text...

    Get Field from Delimited Text String

    Get "Reversed" Field from Delimited Text String

    there are times when you might want to do so inside your own VB code. Now, there is nothing hard about doing this, you would simply use the Split function, and that is all I am doing here, but I thought it might be useful to combine the two functionalities of the above two cited articles into a single function that can be called as needed, so, here is that function....
    Code:
    Function GetField(TextIn As String, Delimiter As String, FieldNumber As Long, _
                      Optional FromFront As Boolean = True, _
                      Optional CaseSensitive As Boolean = False) As Variant
      Dim Fields() As String
      Fields = Split(TextIn, Delimiter, , Abs(CaseSensitive))
      If FromFront Then
        GetField = Fields(FieldNumber - 1)
      Else
        GetField = Fields(UBound(Fields) - FieldNumber + 1)
      End If
    End Function
    The function has three required arguments and two optional arguments. The first required argument is the text string that you want to parse, the second required argument is the text (one or more characters) to use as the delimiter and the third required argument is the field number that you want the function to return (first field is numbered 1, second field is numbered 2, etc.). The optional fourth argument specifies whether the fields should be counted from the front (left to right) or the back (right to left) of the text being parsed... the default for this argument is True meaning count the fields from the front, set it to False to have the function count fields from the back. And the optional fifth argument controls whether the delimiter should be case sensitive or not (True for case sensitive, False for not case sensitive).
    Last edited by Rick Rothstein; 05-01-2013 at 07:50 PM.

Similar Threads

  1. Get "Reversed" Field from Delimited Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 3
    Last Post: 02-22-2015, 09:01 AM
  2. Reversing a "First Middle Last" Name to "Last, First Middle" Name Format
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-06-2014, 10:04 PM
  3. Replies: 5
    Last Post: 04-18-2013, 02:30 AM
  4. VBA to avoid - "indirect" Formula
    By leopaulc in forum Excel Help
    Replies: 2
    Last Post: 10-23-2012, 05:01 PM
  5. Ordinal Suffix (i.e., "st", "nd", "rd" and "th")
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 0
    Last Post: 03-20-2012, 03:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •