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

**Rick Rothstein** · 05-01-2013, 07:23 PM

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).

**Elijah Tice** · 05-18-2017, 06:15 PM

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

**Rick Rothstein** · 05-18-2017, 08:05 PM

**Elijah Tice** · 05-18-2017, 08:27 PM

**Elijah Tice** · 06-02-2017, 06:15 PM

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!