I'm trying create a written out list from a delimited list.
For example "Apple" "Apple|Peach" "Apple|Peach|Pear" will be written out as:
Apple
Apple and Peach
Apple, Peach and Pear
A few of you here can usually do in 5 lines of code what takes me ten. Any suggestion to streamline the following:
[Option Explicit Sub Demo() MsgBox fcnLineList("Apple") MsgBox fcnLineList("Apple|Peach") MsgBox fcnLineList("Apple|Peach|Pear") MsgBox fcnLineList("Apple|Peach|Pear|Plum") MsgBox fcnLineList("Apple|Peach|...|...|...|Pear|Plum") End Sub Function fcnLineList(ByRef strIn As String) As String Dim arrIn() As String Dim lngIndex As Long If Right(strIn, 1) = "|" Then strIn = Left(strIn, Len(strIn) - 1) arrIn = Split(strIn, "|") Select Case UBound(arrIn) Case 0 fcnLineList = arrIn(0) Case 1 fcnLineList = arrIn(0) & " and " & arrIn(1) Case Else For lngIndex = 0 To UBound(arrIn) - 2 fcnLineList = fcnLineList & arrIn(lngIndex) & ", " Next lngIndex fcnLineList = fcnLineList & arrIn(UBound(arrIn) - 1) & " and " & arrIn(UBound(arrIn)) End Select End Function