Help simplifying and/or redo my attempt

[Okkelmo]

Hi
This is my first post on this forum, but not my first time on this forum. I frequently return here to get my problems solved.

After some Googeling, trial and error - I have come up with a solution to what I want, but now I seek help in simplifying my formula or even a total different way to solve what I’m trying to do. Any help from this community is very welcome. Both VBA and non-VBA solutions are ok.

Challenge;

I do an export to Excel and want to extract up to 3 parts from a cell. The trigger is the character $
I want to extract the text between the 1st & 2nd $, the 3rd & 4th $ and the 5th & 6th $. The cell can contain no $’s or several $’s.

A1:

Text before first ‘FindN’ character $ Text after first ‘FindN’ character and before second ‘FindN’ character $ Text after second ‘FindN’ character and before third ‘FindN’ character $ Text after third ‘FindN’ character and before fourth ‘FindN’ character $ Text after fourth ‘FindN’ character and before fifth ‘FindN’ character $ Text after fifth ‘FindN’ character and before sixth ‘FindN’ character $ Text after sixth ‘FindN’ character


Output will look like this:

Text after first ‘FindN’ character and before second ‘FindN’ character

Text after third ‘FindN’ character and before fourth ‘FindN’ character

Text after fifth ‘FindN’ character and before sixth ‘FindN’ character

---

I use the formula:

=IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"$",""))>=6,
(MID(A1,FindN("$",A1,1)+1,FindN("$",A1,2)+1-FindN("$",A1,1)-2)&CHAR(10)&CHAR(10)&
MID(A1,FindN("$",A1,3)+1,FindN("$",A1,4)-FindN("$",A1,3)-1)&CHAR(10)&CHAR(10)&
MID(A1,FindN("$",A1,5)+1,FindN("$",A1,6)-FindN("$",A1,5)-1)),
IF(LEN(A1)-LEN(SUBSTITUTE(UPPER(I2),"$",""))>=4,
MID(A1,FindN("$",A1,1)+1,FindN("$",A1,2)+1-FindN("$",A1,1)-2)&CHAR(10)&CHAR(10)&
MID(A1,FindN("$",A1,3)+1,FindN("$",A1,4)-FindN("$",A1,3)-1),
IF(LEN(I2)-LEN(SUBSTITUTE(UPPER(I2),"$",""))>=2,
MID(A1,FindN("$",A1,1)+1,FindN("$",A1,2)+1-FindN("$",A1,1)-2),"N/A")))

FindN() is

Function FindN(sFindWhat As String, _
sInputString As String, N As Integer) As Integer
Dim J As Integer
Application.Volatile
FindN = 0
For J = 1 To N
FindN = InStr(FindN + 1, sInputString, sFindWhat)
If FindN = 0 Then Exit For
Next
End Function

 

[Jerry Sullivan]

Hi Okkelmo,

If you are going to employ a UDF, I'd suggest doing it in a way that balances making the worksheet formulas simple and adaptable through the use of the parameters.

Here's one way...

Public Function GetMyStrings(sText As String, sDelimiter As String, _
   ParamArray vPartsToDisplay() As Variant) As Variant
     
'--concatenates specified substrings from a delimited input string.
'  the returned value is separated by linefeeds or other characters.
   
'Example: Return substrings 1,3, and 5 from the value in cell A1 
'   (where substring 1 occurs between the 1st and 2nd instance of "$")

' formula syntax:
'    =GetMyStrings(A1,"$",1,3,5)
'  where cell A1 holds value: "Alpha$Bravo$Charlie$Delta$Echo$Foxtrot
'  returns: ("<>" denotes value of sReturnSep variable)
'  "Bravo <> Delta <> Foxtrot"
 
 Dim lPart As Long, lNdx As Long
 Dim sReturn As String, sReturnSep As String
 Dim vParts As Variant

 Application.Volatile
 
 '--this is the separator that will be used btwn parts in returned val
 sReturnSep = Chr(10) & Chr(10)

 vParts = Split(sText, sDelimiter)
 
 For lNdx = LBound(vPartsToDisplay) To UBound(vPartsToDisplay)
   lPart = vPartsToDisplay(lNdx)
   If lPart >= LBound(vParts) And lPart <= UBound(vParts) Then
      sReturn = sReturn & sReturnSep & vParts(lPart)
   End If
 Next
 
 If Len(sReturn) > 0 Then
   sReturn = Mid(sReturn, Len(sReturnSep) + 1)
 End If

 GetMyStrings = sReturn
End Function

 

[Okkelmo]

Hi

Thank you for the fast and great reply.
I have little knowledge of VBA - but copy/paste your example resulted in excactly what I wanted.

Again - thank you!