Lambda function to extract strings between characters?

[dejhantulip]

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:

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

 

[mart37]

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]

Great! Amazing! Thank you sooo much mart37!

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]

I'm fairly new at LAMBDA functions but try this one.

=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
	A	B
1	D6PQT2 | 0001934 | Excavation of natural soil | 005	0001934
2		Excavation of natural soil
3		D6PQT2
4		005
Sheet1

Cell Formulas
Range	Formula
B1	B1	=GETBETWEEN("|",2,1,A1)
B2	B2	=GETBETWEEN("|",2,2,A1)
B3	B3	=GETBETWEEN("|",1,1,A1)
B4	B4	=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

=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,"")))

 

[dejhantulip]

aaaaaaand AMAZING!!!

Great!! Thank you so much!!

Kind regards dear Peter!


-ORLANDO

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.