Eliminating quotes

[franklinsam]

I have the following Lambda function
=LAMBDA(text,key,
LET(
components, TEXTSPLIT(text, "/"),
keyComponent, FILTER(components, ISNUMBER(SEARCH(key, components))),
keyValue, IF(
AND(LEN(keyComponent) > 0, ISNUMBER(--MID(keyComponent, SEARCH("$", keyComponent) + 1, LEN(keyComponent) - SEARCH("$", keyComponent)))),
MID(keyComponent, SEARCH("$", keyComponent) + 1, LEN(keyComponent) - SEARCH("$", keyComponent)),
"Invalid"
),
IF(keyValue = "Invalid", "Invalid input", keyValue)
)
)(A1,adm)
The contents of A1 looks like this energy7%/adm$5/lock$15/fuel$13

This code fails because Search (or Find) require that the input parameters look like this (A1,"adm") and not like this (A1,adm). Any thoughts on how to get around the quotes as I know my users an added the quotes would be a chore for them. BTW, I'm not married to this code so if you have a better solution I'm all getting the job done not who did it.

Any help would be greatly appreciated.

 

[Fluff]

You can slim down the formula like

LET(
components, TEXTSPLIT(text, "/"),
keyComponent, FILTER(components, ISNUMBER(SEARCH(key, components))),
IFERROR(--TEXTAFTER(keyComponent,"$"),"Invalid input")
)
)(A2,"adm")

I don't understand why it's so difficult to enter "adm" rather than adm

 

[franklinsam]

I never said it was difficult. I view my role as someone to make life simpler/ easier/ etc. If they don't have to use the quotes that provides a service. If they have to use the quotes, that is an option, but not the first one.

 

[Fluff]

Personally I don't think it makes it any easier by not typing the quotes. But to answer your question, I don't think it's possible.

 

[franklinsam]

Thanks

 

[Fluff]

You're welcome.

 

[Fluff]

Not sure if this would work for you, but you could put the Key into another cell & pass that to the lambda

=LAMBDA(text,key,LET(
components, TEXTSPLIT(text, "/"),
keyComponent, FILTER(components, ISNUMBER(SEARCH(key, components))),
IFERROR(--TEXTAFTER(keyComponent,"$"),"Invalid input")
))(A1,A2)

 

[franklinsam]

I'm working on a different solution that doesn't require the user to input what they want to get from the string. This is a fragment that will get energy regardless of if it is a $ or a %. Needs more work but it is a much better start
=LET(
base, 50,
parm1, INDEX(TEXTSPLIT(A12,"/"),1),
energy, INDEX(TEXTSPLIT(parm1,"adm"),2),
PerCent, IF(IFERROR(FIND("%",energy,1),0),base*(1+energy),0),
Dollar, IF(IFERROR(FIND("$",energy,1),0),energy,0),
Value, PerCent + Dollar,
Value
)