Help simplifying and/or redo my attempt

Okkelmo

New Member
Joined
Feb 9, 2015
Messages
2
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:

Code:
=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
Code:
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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...

Code:
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

New Member
Joined
Feb 9, 2015
Messages
2
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!
 

Forum statistics

Threads
1,141,403
Messages
5,706,252
Members
421,434
Latest member
DaltonB

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top