Lambda function to extract strings between characters?

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hello everyone! :)

So I have been messing around lately with some data that has delimiters and I am trying (as an alternative to powerquery) to extract some strings that are in the raw data cells using the LAMBDA function.

Could anyone help me out to see how I could use lambda for this?

So let's say I have the following:

D6PQT2 | 0001934 | Excavation of natural soil | 005

So I would like a lambda funcion where I could input the delimiting character (in this case "|") and the number of the instance where lambda would return the string AFTER or BEFORE the instance according to the syntax.

So maybe something like this:
Excel Formula:
LAMBDA(d,i,b,s)("|",2,1,cell)
where
d = delimiter
i = instance of delimiter
b = before the delimiter would be the number '1', after the delimiter would be number '2' (kind of like a "switch" to decide if it would have to return the string before or after the delimiter)
s = the cell that has the complete string
result = in this case the result would have to be 0001934 (formatted as a text since I need the three zeroes at the beginning to be extracted and shown), also no extra spaces at the beginning or end (but in the case of the "Excavation of natural soil" the spaces in the middle of the words would have to be preserved.

What I have just described is something I have in my mind and how I picture the solution to be, however I am open to any type of ideas or something that would help me out to get what I need.

Would this be possible? Could someone help me out with this please?

Thank you very very much in advance :)
Have a great day!


ORLANDO
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Function Lambada(d As String, i As Integer, b As Integer, s As Range) As String
    Dim x As Integer, z As Integer, y As Integer
    Dim ii As Integer, xx As Integer, zz As Integer, yy As Integer
    Dim t As String
    y = 1
    For x = 1 To i
        z = InStr(y, s.Value, d, vbBinaryCompare)
        If z > 0 Then
            y = z + 1
        Else
            z = 0
        End If
    Next
    If z > 0 Then
        ii = IIf(b = 1, i - 1, i + 1)
        yy = 1
        For xx = 1 To ii
            zz = InStr(yy, s.Value, d, vbBinaryCompare)
            If zz > 0 Then
                yy = zz + 1
            Else
                zz = IIf(b = 1, 0, Len(s.Value) + 1)
            End If
        Next
    End If
    If b = 1 And z > 0 Then
        Lambada = Trim(Mid(s.Value, zz + 1, z - zz - 1))
    ElseIf b = 2 And zz > 0 Then
        Lambada = Trim(Mid(s.Value, z + 1, zz - z - 1))
    Else
        Lambada = ""
    End If
End Function
 

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Great! Amazing! Thank you sooo much mart37! :cool:

However I was looking for a lambda function (as in the "new" LAMBDA function in Office 365).

If anyone could help me out with this, I will be greatly thankful :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,540
Office Version
  1. 365
Platform
  1. Windows
I'm fairly new at LAMBDA functions but try this one.
Excel Formula:
=LAMBDA(d,i,b,s,TRIM(REPLACE(LEFT(s,SEARCH(d&d,SUBSTITUTE(s&d,d,d&d,i+(b=2)))-1),1,SEARCH(d&d,SUBSTITUTE(d&s,d,d&d,i+(b=2)))-1,"")))

I named mine as GETBETWEEN

dejhantulip.xlsm
AB
1D6PQT2 | 0001934 | Excavation of natural soil | 0050001934
2Excavation of natural soil
3D6PQT2
4005
Sheet1
Cell Formulas
RangeFormula
B1B1=GETBETWEEN("|",2,1,A1)
B2B2=GETBETWEEN("|",2,2,A1)
B3B3=GETBETWEEN("|",1,1,A1)
B4B4=GETBETWEEN("|",3,2,A1)


Note:
You might consider changing the 'b' variable to 'a' for "after" and using the values 1 (for True) and 0 (for False) as with the use of 1 & 0 like this in other Excel functions.
That is '1' would mean after the delimiter and '0' would mean before the delimiter.
Then the function would become a tiny bit simpler

Rich (BB code):
=LAMBDA(d,i,a,s,TRIM(REPLACE(LEFT(s,SEARCH(d&d,SUBSTITUTE(s&d,d,d&d,i+a))-1),1,SEARCH(d&d,SUBSTITUTE(d&s,d,d&d,i+a))-1,"")))
 
Last edited:
Solution

dejhantulip

New Member
Joined
Sep 9, 2015
Messages
34
Office Version
  1. 365
Platform
  1. Windows
aaaaaaand AMAZING!!!

Great!! Thank you so much!! :) :)

Kind regards dear Peter!


-ORLANDO
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,540
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,915
Messages
5,638,970
Members
417,062
Latest member
Canucks21

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