Forum Discussion
How to create a Custom formula in the Custom-Ribbon, instead of Name-Manager
Dear Experts,
For example, if I have to split a string into Char and numbers, I used a Custom formula using LET as below in the "Name Manager"
Problem with this is that we can't use this formula in other Workbooks, So , I need to find a way to add this to myCustom Ribbon:-
How to do this?
Thanks in Advance,
Br,
Anupam
The definition of textafterlastdigit should be:
=LAMBDA(a,TEXTAFTER(a, SEQUENCE(10,,0),-1,,,""))
See the attached version:
The definition of textafterlastdigit should be:
=LAMBDA(a,TEXTAFTER(a, SEQUENCE(10,,0),-1,,,""))
See the attached version:
The old 'Explode' Lambda certainly works but there's an easier way if you have access to Regex:
=REGEXEXTRACT(A1,".",1)The "." indicates any character. "1" is telling Excel to return each instance of any character which converts scalar to array.
Thanks Patrick, this regex function seems a life saver in excel, but unfortunately in our company IT desk doesn't enabled it :( in excel.
Thanks HansVogelaar , Sorry I didn't understood fully, yes this is what my goal is to get it in an addin
I attach the spread sheet, as an example, can you please help share the steps on how you add it to say the Addin "Anupam_Tools",
Or any simple lambda formula, intent is to understand how we bring this inbuilt/Custom formula to my "Anupam_Tools" the custom ribbon
Thanks in Advance,
The lambda formula is
=LAMBDA(a, MID(a, SEQUENCE(, LEN(a)), 1))
I don't think adding it to a custom ribbon tab is a good idea.
You could create a named lambda formula in your PERSONAL.XLSB workbook (or in an add-in).
You can do so without unhiding PERSONAL.XLSB by executing the following line in the Immediate window in the Visual Basic Editor:
Workbooks("personal.xlsb").Names.Add Name:="Split_Char", RefersTo:="=LAMBDA(a, MID(a, SEQUENCE(, LEN(a)), 1))"You can then use it as follows to split the value of A1 in any workbook:
=PERSONAL.XLSB!Split_Char(A1)Thanks HansVogelaar , as an example:-
I tried an example as above, with normal formula ( in this case to extract txt after the last digit in each string) , it works without LET/LAMBDA, but when I use LAMBDA in Name-Manager, then it gives an Err as above in E2. Attached is the worksheet.
Thanks in Advance,
Br,
Anupam
