dejhantulip
New Member
 Joined
 Sep 9, 2015
 Messages
 34
 Office Version

 365
 Platform

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