Results 1 to 5 of 5

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

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    628
    Rep Power
    6

    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 08:20 PM.

  2. #2
    Junior Member
    Join Date
    May 2017
    Posts
    3
    Rep Power
    0
    Rick,
    Thank you very much for your work on this site! Your mini blog has been very helpful for me, and I appreciate you sharing your knowledge

    I came across your "GetField" function and it is very close to what I've been trying to do for quite a while now: I have a worksheet with a column with dates in it - each cell in that "dates" column can have any number of dates (thanks to your LookUpConcat function, by the way!) but typically not more than eight (column A in the screenshot below). What I need to do is check each one of those dates against another date (column B) to see if they come before, or on/after that other date, then return "Before" or "On/After" in the next column (column C below). I tried combining the GetField with an IF function but the eval stops as soon as it hit a False.

    Capture.JPG

    Is it possible to modify this UDF to do what I'm asking?

    Thank you very much for your time!

    EDIT: I'm using Excel 2010
    Last edited by Elijah Tice; 05-18-2017 at 07:43 PM.

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    628
    Rep Power
    6
    A couple of questions...

    1) Are the dates in each cell always in sorted order as shown in your picture?

    2) Is the delimiter between the dates a comma by itself or a comma followed by a Line Feed? In other words, did you press ALT+Enter after each comma?

  4. #4
    Junior Member
    Join Date
    May 2017
    Posts
    3
    Rep Power
    0
    1) Yes, they are always sorted.
    2) Comma followed by Line Feed.

    Thank you!

  5. #5
    Junior Member
    Join Date
    May 2017
    Posts
    3
    Rep Power
    0
    Good morning!
    I've still been searching for a way to do what I describe above but haven't had any luck - is it possible or am I reaching beyond what Excel can do?

    Thank you!

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:31 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:34 PM
  3. Replies: 5
    Last Post: 04-18-2013, 03:00 AM
  4. VBA to avoid - "indirect" Formula
    By leopaulc in forum Excel Help
    Replies: 2
    Last Post: 10-23-2012, 05:31 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, 04:16 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
  •