> GetNthPart    


Synopsis

Get the Nth part in a string, number, or date. Parts are separated by whatever delimiter you specify.

CODE
' GetNthPart
' http://www.utteraccess.com/wiki/GetNthPart
' Code courtesy of UtterAccess Wiki
' Licensed under Creative Commons License
' http://creativecommons.org/licenses/by-sa/3.0/
'
' You are free to use this code in any application,
' provided this notice is left unchanged.
'
' rev  date                          brief descripton
' 1.0  2019-07-16
-                    
'
'*************** Code Start *****************************************************
' Purpose  : Get the Nth Part in a string
' Return   : Variant -- Null or String
'--------------------------------------------------------------------------------
'                              GetNthPart
'--------------------------------------------------------------------------------
Function GetNthPart( pvString As Variant _
  , piPart As Integer  _
  , Optional psDeli As String = "-" _
  ) As Variant
' strive4peace
'  RETURNS a specified part # of a string
  'PARAMETERS
  '  pvString = string to evaluate
  '  piPart = part number to return
  '  psDeli = delimiter between parts
  ' ?Get_NthPart("abc-def-ghi-jkl",3,"-")="ghi"
 
  On Error Resume Next  
  'initialize return value to be Null
  GetNthPart = Null  
  'subtract 1 from piPart since array index starts with 0
  GetNthPart = Split(pvString, psDeli)(piPart - 1)
End Function
'*************** Code End *******************************************************

Creative Commons License
GetNthPart by UtterAccess Wiki is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Editing and revision of the content is freely encouraged; for details, see Expected Usage.

EXAMPLES:

Get the second part of a code, where parts are separated with dashes:

  • GetNthPart("190705-STYLE-999-1016", 2, "-") = "STYLE"

Get the decimal part of a number Even though a number is sent, it will be implicitly converted to a string:

  • GetNthPart(123.45, 2, ".") = "45"

Get the thousands part of a number in the millions:

  • GetNthPart( Format(1234567, "#,##0"), 2, ",") = "234"

Get the top level folder in a URL path. "%2F" is the representation for "/":

  • GetNthPart("http%3A%2F%2FFolder1%2FFolder2", 3, "%2F") = "Folder1"

Get year part of a date:

  • GetNthPart(#8/1/2019#, 3, "/") = "2019"

LOGIC:

  • Pass the string to parse, the part number you want to extract, and optionally, a delimiter, which can be multiple characters. If no delimiter is specified, dash ("-") will be used.
  • Skip all errors.
  • Initialize the return value to be Null in case the function can't be evaluated.
  • If nothing was passed in the string, then exit.
  • Use the Split function to separate the string into Parts, using what is in the psDeli variable to delimit.
  • Subtract 1 from the specified element number since the first index in an array is 0 not 1. Then extract the desired part and assign it to the return value of the function.
  • If the expression can't be evaluated, Null is returned.
  • This VBA can run from Access ... or Excel, Part, PowerPoint, Project, Visio, ... or other Microsoft Office VBA interface. There is nothing in it that requires Access. It is pure VBA!

PARAMETERS:

  • pvString is the string to parse. Defined as a variant so it can be Null or another data type.
  • piPart is the Part number to get.

OPTIONAL PARAMETER:

  • psDeli is the delimiter. Default is dash ("-").