# Help simplifying and/or redo my attempt

#### Okkelmo

##### New Member
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
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
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!

Replies
3
Views
175
Replies
5
Views
94
Replies
5
Views
162
Replies
8
Views
289
Replies
7
Views
356

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.

### Which adblocker are you using?

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

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