Get_NthPart function Ms Access Gurus
Home Code

VBA > Function > Get Nth Part

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

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

Optional:

Code


'*************** Code Start *****************************************************
' Purpose  : Get the Nth Part in a string
' Author   : crystal (strive4peace) 
' Return   : String
' License  : below code
' Code List: www.MsAccessGurus.com/code.htm
'--------------------------------------------------------------------------------

'                              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
'
' LICENSE
'   You may freely use and share this code 
'     provided this license notice and comment lines are not changed;
'     code may be modified provided you clearly note your changes.
'   You may not sell this code alone, or as part of a collection,
'     without my handwritten permission.
'   All ownership rights reserved. Use at your own risk.  
'   ~ crystal (strive4peace)  www.MsAccessGurus.com
'*************** Code End *******************************************************

'--------------------------------------------------------------------------------

'                              test_GetNthPart
'--------------------------------------------------------------------------------
Sub test_GetNthPart ()
   'click HERE and press F5 to run
   'customize this example if you wish
   MsgBox GetNthPart("190705-STYLE-999-1016", 2, "-") _
      , , "test GetNthPart"
End Sub

Reference

VBA > Function > Get Nth Word

For a function with more flexibility, see GetNthWord function

Docs / Office VBA Reference / Language Reference / Functions / Split

Help: Split function

Share

Share with others ... here's the link to copy:
https://MsAccessGurus.com/VBA/Code/Fx_GetNthPart.htm

Do you have something to say or share?

It is interesting to hear from you. Was something not clear? Did you find a bug? Is an explanation wrong or not sufficient? Do you want the code do more (there is always more)?

Some of you write to say thanks and tell me what you're doing with Access ... its nice to get an echo back. I want you and others to be good with Access, and other Office applications like Excel, Word, and PowerPoint ... and Windows. Take advantage of the strengths in each to manage your information wisely.

Are you a developer? Do you want to share? Email to ask about getting your pages added to the code index.

When we communicate, collaborate, and appreciate, we all get better. Thank you. Email me at info@msAccessGurus.com

Goto Top