Eliminating quotes

franklinsam

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
You can slim down the formula like
Excel Formula:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Not sure if this would work for you, but you could put the Key into another cell & pass that to the lambda
Excel Formula:
=LAMBDA(text,key,LET(
components, TEXTSPLIT(text, "/"),
keyComponent, FILTER(components, ISNUMBER(SEARCH(key, components))),
IFERROR(--TEXTAFTER(keyComponent,"$"),"Invalid input")
))(A1,A2)
 
Upvote 0
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
)
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

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
Back
Top