franklinsam
New Member
- Joined
- Oct 1, 2023
- Messages
- 17
- Office Version
- 365
- Platform
- Windows
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.
=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.