Skip to content

Instantly share code, notes, and snippets.

@Softwaretrain

Softwaretrain/gist:c5e59aafb1862a217852cc742d18d40e

Last active August 12, 2025 07:10
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save Softwaretrain/c5e59aafb1862a217852cc742d18d40e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save Softwaretrain/c5e59aafb1862a217852cc742d18d40e to your computer and use it in GitHub Desktop.
Download ZIP
Mahmoud Lambda Functions
Raw
gistfile1.txt
/*
FUNCTION NAME: ABH
VERSION:1.0
AUTHOR: Mahmoud Bani Asadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION:
This function converts a numeric value (integer or decimal) into its full Persian text representation.
It handles negative numbers by prefixing "منفی"، separates the integer part into named segments
(تریلیارد، میلیارد، میلیون، هزار)، and converts the fractional part into fractional units
(دهم، صدم، هزارم، etc.).
The function supports up to 15 digits in the integer part and up to 6 digits in the fractional part.
ARGUMENTS:
number: The numeric value (integer or decimal) to convert to Persian text.
RETURNS:
A text string representing the input number in Persian words.
EXAMPLE USAGE:
=ABH(-548654) --> "منفی پانصد و چهل و هشت هزار و ششصد و پنجاه و چهار"
=ABH(0.54) --> "پنجاه و چهار صدم"
=ABH(1234567890.005) --> "یک میلیارد و دویست و سی و چهار میلیون و پانصد و شصت و هفت هزار و هشتصد و نود ممیز پنج هزارم"
*/
ABH= LAMBDA(number,
LET(
num,number,
sign,IF(num<0,"منفی ",""),
numAbs,ABS(num),
intPart,INT(numAbs),
decRaw,TEXT(numAbs,"0."&REPT("#",15)),
decAll,IFERROR(TEXTAFTER(decRaw,"."),""),
decUsed,LEFT(decAll,6),
decLen,LEN(decUsed),
a,TEXT(intPart,REPT(0,15)),
w,LAMBDA(x,
LET(
h,CHOOSE(--MID(x,1,1)+1,"","صد","دویست","سیصد","چهارصد","پانصد","ششصد","هفتصد","هشتصد","نهصد"),
t,CHOOSE(--MID(x,2,1)+1,"","","بیست","سی","چهل","پنجاه","شصت","هفتاد","هشتاد","نود"),
o,IF(--MID(x,2,1)<>1,
CHOOSE(--MID(x,3,1)+1,"","یک","دو","سه","چهار","پنج","شش","هفت","هشت","نه"),
CHOOSE(--MID(x,3,1)+1,"ده","یازده","دوازده","سیزده","چهارده","پانزده","شانزده","هفده","هجده","نوزده")
),
TRIM(h & IF(AND(h<>"",OR(t<>"",o<>""))," و ","") & t & IF(AND(t<>"",o<>"")," و ","") & o)
)
),
intText,IF(a=REPT(0,15),"صفر",
TEXTJOIN(" و ",,
IF(MID(a,1,3)<>"000",w(MID(a,1,3))&" تریلیارد",""),
IF(MID(a,4,3)<>"000",w(MID(a,4,3))&" میلیارد",""),
IF(MID(a,7,3)<>"000",w(MID(a,7,3))&" میلیون",""),
IF(MID(a,10,3)<>"000",w(MID(a,10,3))&" هزار",""),
IF(MID(a,13,3)<>"000",w(MID(a,13,3)),"")
)
),
decText,IF(decUsed="","", IF(VALUE(decUsed)=0,"",
LET(
decPad, TEXT(VALUE(decUsed),"000000"),
decWords, TEXTJOIN(" و ",,
IF(MID(decPad,1,3)<>"000", w(MID(decPad,1,3)) & " هزار",""),
IF(MID(decPad,4,3)<>"000", w(MID(decPad,4,3)), "")
),
unit,CHOOSE(decLen,"دهم","صدم","هزارم","ده هزارم","صد هزارم","میلیونم"),
decWords & " " & unit
)
)),
result,
IF(decText<>"",
sign & IF(AND(intText="صفر", numAbs<1),
decText,
intText & " ممیز " & decText),
sign & intText),
IF(NOT(ISNUMBER(num)),"", result)
))
;
/*
FUNCTION NAME: Fxml
VERSION:1.0
AUTHOR: Mahmoud Bani Asadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Parses a delimited text string into XML nodes and returns the nodes that match the given XPath expression.
This function splits the input text by the specified delimiter, wraps each segment as an XML element, then applies the XPath query to select and return the matching nodes or values.
ARGS:
text: The input string containing segments separated by the delimiter.
delimiter: The character or string used to split the text into parts.
xpath: The XPath expression used to select nodes or values from the constructed XML.
EXAMPLE:
=Fxml("ABC|DEFG|123|Xc|Wizard|4567","|","[.=number()]")
*/
Fxml=LAMBDA(text,delimiter,xpath,
FILTERXML("<t><m>" & SUBSTITUTE(text, delimiter, "</m><m>") & "</m></t>", "//m" & xpath))
;
/*
FUNCTION NAME: J_EOMONTH
VERSION:1.0
AUTHOR: Mahmoud Bani Asadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Return the serial number of the last Persian day of the month before or after a specific number of months.
ARGS:
start_date: is a serial date number that represents the start Persian date.
months: is the number of months before or after the start_date.
EXAMPLE:
=J_EOMONTH(Today(),0)
*/
J_EOMONTH=LAMBDA(start_date,months,
LET(
a,TEXT(start_date,"[$-fa,16]yyy/mm/dd"),
b,LEFT(a,4),
c,MID(a,6,2),
d,RIGHT(a,2),
e,SIGN(months)=-1,
f,SEQUENCE(31*(ABS(months)+1)-1,,start_date,IF(e,-1,1)),
g,TEXT(f,"[$-fa,16]yyy/mm/dd"),
h,MOD(c+months,12),
i,INT((c+months)/12.1),
j,b+i&"/"&TEXT(IF(h,h,12),"00"),
XLOOKUP(j,LEFT(g,7),f,,,IF(e,1,-1))
))
;
/*
FUNCTION NAME: InsertBlankRow
VERSION:1.0
AUTHOR: Mahmoud Bani Asadi > Idea of this formula is comming from this post: https://www.youtube.com/watch?v=a7dZnBBb5Yg
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Insert blank row at every value change dynamically
ARGS:
table_array: a table that we want to have blank row after each group of data (include header).
col_num : column index number that we want to insert blank row based on each group of data.
blank_row : count of blank row that you want to have between each group of data.
EXAMPLE:
=InsertBlankRow(Table1[#All],2,1)
*/
InsertBlankRow=LAMBDA(tbl,col_num,blank_row,
LET(
rng,DROP(tbl,1),
hd,TAKE(tbl,1),
srt,SORT(rng,col_num),
sq,SEQUENCE(ROWS(rng)),
result,REDUCE("",sq,LAMBDA(a,c,
LET(
d,INDEX(rng,c,col_num),
e,INDEX(rng,c+1,col_num),
f,INDEX(rng,c,),
g,IF(d=e,f,EXPAND(f,1+blank_row,,"")),
h,IFERROR(g,f),
i,VSTACK(a,h),
i )
)
),
VSTACK(hd,DROP(result,1))
))
;
/*
FUNCTION NAME: FillDown
VERSION:1.0
AUTHOR: Mahmoud Bani Asadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Fill down data of selected array.
ARGS:
fill_array: a column of table that we want to fill down. (if you select one row it will fill right)
EXAMPLE:
=FillDown(A2:C10)
*/
FillDown=LAMBDA(fill_array,
LET(
tocol,TOCOL(fill_array,,1),
filldown,SCAN("",tocol,LAMBDA(a,c,IF(c="",a,c))),
result,TRANSPOSE(WRAPROWS(filldown,ROWS(fill_array))),result))
;
/*
FUNCTION NAME: SplitAmount
VERSION:1.0
AUTHOR: Jvdv
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Split each number to a specific number.
ARGS:
InitialNames: a column include lables.
InitialAccountNumbers: a column include account numbers.
InitialTransferAmounts: a column include amounts to split to a nth number of devision.
MaxTransferLimit: amount which is the maximum transfer limit.
EXAMPLE:
=SplitAmount(Table[name],Table[account],Table[Amount],50000000)
*/
SplitAmount=LAMBDA(InitialNames,InitialAccountNumbers,InitialTransferAmounts,MaxTransferLimit,
LET(
RequiredTransfers, SEQUENCE(,MAX(CEILING(InitialTransferAmounts/MaxTransferLimit,1))),
IsTransferRequired, RequiredTransfers<InitialTransferAmounts/MaxTransferLimit+1,
TransferNames, IF(IsTransferRequired,InitialNames,NA()),
TransferAccounts, IF(IsTransferRequired,InitialAccountNumbers,NA()),
TransferAmountsModulus, MOD(InitialTransferAmounts,MaxTransferLimit),
TransferAmounts, IF(RequiredTransfers*MaxTransferLimit>InitialTransferAmounts,TransferAmountsModulus,MaxTransferLimit),
FilteredTransferAmounts, IF(IsTransferRequired,TransferAmounts,NA()),
StackedData, VSTACK(TransferNames,TransferAccounts,FilteredTransferAmounts),
TwoColData, TOCOL(StackedData,2),
WrappedData, WRAPCOLS(TwoColData,SUM(--IsTransferRequired)),
WrappedData
))
;
/*
FUNCTION NAME: RepeatN
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Repeat data N times.
ARGS:
array: a column include data to repeat.
count: number of time to repeat each item in array.
EXAMPLE:
=RepeatN(Table[Name],Table[Count])
*/
RepeatN=LAMBDA(array,count,
LET(
a,SCAN(0,count,LAMBDA(x,y,x+y))-count+1,
b,SEQUENCE(SUM(count)),
c,SCAN(0,b,LAMBDA(g,h,XLOOKUP(h,a,array,g))),
c))
;
/*
FUNCTION NAME: UNPIVOT
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Unpivot data.
ARGS:
table_array: table to unpivot all columns except first one.
EXAMPLE:
=UNPIVOT(Table)
*/
UNPIVOT=LAMBDA(tbl,LET(
r,DROP(TAKE(tbl,1),,1),
c,DROP(TAKE(tbl,,1),1),
d,DROP(tbl,1,1),
f,TOCOL(IFNA(c,r)),
s,TOCOL(IFNA(r,c)),
result,HSTACK(f,s,TOCOL(d)),result))
;
/*
FUNCTION NAME: MXLOOKUP
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Vlookup multiple data.
ARGS:
lookup_value: value that we are searching for it.
lookup_array: Array which has our value.
return_array: Array which has our result.
if_not_found: Specify a value if result be blank.
duplicate: True/False to set you need duplicates or just you want unique result.
EXAMPLE:
=MXLOOKUP("value",Array,Result,"",false)
*/
MXLOOKUP=LAMBDA(lookup_value, lookup_array, return_array, if_not_found, duplicate,
LET(
filter, FILTER(
return_array,
lookup_array =
lookup_value,
if_not_found
),
result, TRANSPOSE(
filter
),
dup, IF(
duplicate,
result,
UNIQUE(result, 1)
),
dup
)
)
;
/*
FUNCTION NAME: GETNUMS
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Extract numbers from text string.
ARGS:
text: Text include numbers
EXAMPLE:
=GETNUMS("Invoice No. 548 Mr. Asadi")
*/
GETNUMS=LAMBDA(text,
LET(
t, text,
a, LEN(t),
b, SEQUENCE(a),
c, MID(t, b, 1),
d, ISNUMBER(--c),
e, FILTER(c, d, ""),
f, NUMBERVALUE(TEXTJOIN("", , e)),
f
)
)
;
/*
FUNCTION NAME: TBH
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Return text of year, month and day for Persian date.
ARGS:
DateToText: The Persian date,
[mode]: mode of converting day and year to text or not, if 0 or omitted returns shows all date in text if put anything else just convert month to text,
EXAMPLE:
=TBH("1367/01/25")
*/
TBH=LAMBDA(DateToText,[mode],
LET(
src,IF(ISNUMBER(DateToText),TEXT(DateToText,"[$-fa,ir,16]yyyy/mm/dd"),DateToText),
mod,mode,
year,LEFT(src,FIND("/",src)-1),
month,MID(src,FIND("/",src)+1,2),
day,RIGHT(src,2),
m,{"فروردین","اردیبهشت","خرداد","تیر","مرداد","شهریور","مهر","آبان","آذر","دی","بهمن","اسفند"},
tt,LAMBDA(NumberToText,
LET(
a,TEXT(NumberToText,"0000"),
b,{"ده","یازده","دوازده","سیزده","چهارده","پانزده","شانزده","هفده","هجده","نوزده"},
c,{"","یک","دو","سه","چهار","پنج","شش","هفت","هشت","نه"},
d,{"","صد","دویست","سیصد","چهارصد","پانصد","ششصد","هفتصد","هشتصد","نهصد"},
e,{"","","بیست","سی","چهل","پنجاه","شصت","هفتاد","هشتاد","نود"},
h,
IF(--MID(a,1,1)=0,,IF(--RIGHT(a,3)>0,"هزار و "," هزار"))
&IF(AND(--a=0,NumberToText<>""),"صفر",INDEX(d,MID(a,2,1)+1)
&IF(--MID(a,2,1)=0,,IF(AND(--MID(a,3,1)=0,--MID(a,4,1)=0),," و "))
&INDEX(e,MID(a,3,1)+1)
&IF(OR(--MID(a,3,1)=0,--MID(a,3,1)=1,--MID(a,4,1)=0),," و ")
&IF(--MID(a,3,1)<>1,INDEX(c,MID(a,4,1)+1),INDEX(b,MID(a,4,1)+1))),
h)),
y,IF(mod=0,--year,tt(year)),
mo,INDEX(m,--month),
d,IF(mod=0,--day,tt(day)),
result,CONCAT(d," ",mo," ",y),
result))
;
/*
FUNCTION NAME: JDIFFYMD
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Return year, month and days between two Persian date.
ARGS:
start_date: The Persian date,
[end_date]: The Persian date, if omitted returns current date
EXAMPLE:
=JDIFFYMD("1367/01/25","1400/01/25")
//365, IF( ISOMITTED(end_date),TEXT(end_date,
"[$-fa,ir,16]yyy/mm/dd"),end_date)
*/
JDIFFYMD=LAMBDA(start_date,[end_date],
LET(
e, end_date,
d, RIGHT(start_date,2),
dd, RIGHT(e, 2),
m, MID(start_date,6,2),
mm, MID(e, 6, 2),
y, LEFT(start_date,4),
yy, LEFT(e, 4),
wDays,((yy*360)+(mm*30)+dd)-((y*360)+(m*30)+d),
Year,ROUNDDOWN((wDays/360),0),
Month,ROUNDDOWN((MOD(wDays,360))/30,0),
ESdate, d +IF(
MOD(y, 4) = 3,
INT((y -1299) *365.25) - 1,
INT((y -1299) *365.25)) +
IF(--m < 8,(m - 1) * 31,
(m - 1) * 30 +6) + 7385,
EEdate, dd +IF(
MOD(yy, 4) = 3,
INT((yy -1299) *365.25) - 1,
INT((yy -1299) *365.25)) +
IF(--mm < 8,(mm - 1) * 31,
(mm - 1) * 30 +6) + 7385,
date, SEQUENCE(EEdate - ESdate +5,,ESdate - 1),
Pdate, TEXT(date,"[$-fa,ir,16]yyy/mm/dd"),
ss,SORT(Pdate,,-1),
ii,RIGHT(INDEX(Pdate,SEQUENCE(31)),2),
first, MATCH(RIGHT(start_date,2),ii,0),
last, MATCH(RIGHT(e,2),ii,0),
days, IFERROR(last-first,0),
result,TEXTJOIN("-",,Year,Month,days),
result
)
)
;
// Convert date to Persian date
JJALALDATE = LAMBDA(date,
LET(
Pdate,text(date,"[$-fa,ir,16]yyy/mm/dd"),
Pdate
)
)
;
/*
FUNCTION NAME: JGEREGORIANDATE
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Convert Persian date to Geregorian date.
ARGS:
PersianDate: The Persian date
EXAMPLE:
=JGEREGORIANDATE("1367/01/25")
*/
JGEREGORIANDATE=LAMBDA(PersianDate,
LET(
d, RIGHT(PersianDate, 2),
m, MID(PersianDate, 6, 2),
y, LEFT(PersianDate, 4),
ESdate, d +
IF(
MOD(y, 4) = 3,
INT((y - 1299) * 365.25) - 1,
INT((y - 1299) * 365.25)
) + IF(--m < 8, (m - 1) * 31, (m - 1) * 30 + 6) + 7385,
EEdate, ESdate,
date, SEQUENCE(EEdate - ESdate + 5, , ESdate - 2),
Pdate, TEXT(date,
"[$-fa,ir,16]yyy/mm/dd"
),
first, MATCH(PersianDate, Pdate, 0),
f, INDEX(date, first),
f
)
)
;
/*
FUNCTION NAME: JADDDAY
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: The nth date after/before Persian date
ARGS:
StartDate: The Persian date
NumberToAddOrMinus: The number of days you want to add or subtract(minus for subtracting)
EXAMPLE:
=JADDDAY("1367/01/25",1000)
*/
JADDDAY=LAMBDA(StartDate, NumberToAddOrMinus,
LET(
d, RIGHT(StartDate, 2),
m, MID(StartDate, 6, 2),
y, LEFT(StartDate, 4),
ESdate, d +
IF(
MOD(y, 4) = 3,
INT((y - 1299) * 365.25) - 1,
INT((y - 1299) * 365.25)
) + IF(--m < 8, (m - 1) * 31, (m - 1) * 30 + 6) + 7385,
EEdate, ESdate + NumberToAddOrMinus + 1,
date, SEQUENCE(EEdate - ESdate + 5, , ESdate - 1),
mdate, SEQUENCE(ESdate - EEdate + 5, , EEdate - 1),
Pdate, TEXT(
IF(NumberToAddOrMinus >= 0, date, mdate),
"[$-fa,ir,16]yyy/mm/dd"
),
first, MATCH(StartDate, Pdate, 0),
f, INDEX(Pdate, first + NumberToAddOrMinus),
f
)
)
;
/*
FUNCTION NAME: JDIFF
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Number of dates between two Persian date
ARGS:
start_date: The Persian start date
[end_date]: The Persian end date, if omitted returns current date
EXAMPLE:
=JDIFF("1367/01/25","1400/12/12")
365,IF(ISOMITTED(end_date), TEXT(end_date,"[$-fa,ir,16]yyy/mm/dd"),end_date)
*/
JDIFF=LAMBDA(start_date,[end_date],
LET(
e,end_date,
d, RIGHT(start_date, 2),
dd, RIGHT(e, 2),
m, MID(start_date, 6, 2),
mm, MID(e, 6, 2),
y, LEFT(start_date, 4),
yy, LEFT(e, 4),
ESdate, d +
IF(
MOD(y, 4) = 3,
INT((y - 1299) * 365.25) - 1,
INT((y - 1299) * 365.25)
) + IF(--m < 8, (m - 1) * 31, (m - 1) * 30 + 6) + 7385,
EEdate, dd +
IF(
MOD(yy, 4) = 3,
INT((yy - 1299) * 365.25) - 1,
INT((yy - 1299) * 365.25)
) + IF(--mm < 8, (mm - 1) * 31, (mm - 1) * 30 + 6) + 7385,
date, SEQUENCE(EEdate - ESdate + 5, , ESdate - 1),
Pdate, TEXT(date, "[$-fa,ir,16]yyy/mm/dd"),
first, MATCH(start_date, Pdate, 0),
last, MATCH(e, Pdate, 0),
result,IFERROR(last - first, 0),result
)
)
;
/*
FUNCTION NAME: SPLIT
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Split text by any delimiter
ARGS:
text: The text to split by delimiter
delimiter: The delimiter to split text
EXAMPLE:
=SPLIT("1367/01/25","/")
*/
SPLIT=LAMBDA(text,delimiter,
LET(
s,"<a><b>",
e,"</b></a>",
m,"</b><b>",
r,SUBSTITUTE(text,delimiter,m),
c,CONCAT(s,r,e),
TRANSPOSE(FILTERXML(c,"//b"))))
;
/*
FUNCTION NAME: ABH_INT
VERSION:1.0
AUTHOR: Jack Williams
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Convert Number to Persian text
ARGS:
NumberToText: The number you want to convert to Persian alphabet
EXAMPLE:
=ABH_INT(1500)
*/
ABH_INT
= LAMBDA(NumberToText,
LET(
n, NumberToText,
absN, ABS(n),
a, TEXT(absN, "000000000000000"),
b, {"ده","یازده","دوازده","سیزده","چهارده","پانزده","شانزده","هفده","هجده","نوزده"},
c, {"","یک","دو","سه","چهار","پنج","شش","هفت","هشت","نه"},
d, {"","صد","دویست","سیصد","چهارصد","پانصد","ششصد","هفتصد","هشتصد","نهصد"},
e, {"","","بیست","سی","چهل","پنجاه","شصت","هفتاد","هشتاد","نود"},
L, LAMBDA(p, MID(a, p, 1)),
CH, LAMBDA(x_1,x_2,p, INDEX(x_1, MID(x_2, p, 1) + 1)),
STEP1, LAMBDA(p, IF(--L(p) = 0, , IF(AND(--L(p + 1) = 0, --L(p + 2) = 0), , " و ")) ),
STEP2, LAMBDA(p, IF(OR(--L(p) = 0, --L(p) = 1, --L(p + 1) = 0), , " و ")),
STEP3, LAMBDA(p, IF(--L(p) <> 1, CH(c, a, p + 1), CH(b, a, p + 1))),
STEP4, LAMBDA(l,[r],[txt_1],[txt_2],
CH(d, a, l) & STEP1(l) & CH(e, a, l + 1) & STEP2(l + 1) & STEP3(l + 1) &
IF(
AND(ISOMITTED(r), ISOMITTED(txt_1), ISOMITTED(txt_2)),
"",
IF(--MID(a, l, 3) = 0, , IF(--RIGHT(a, r) > 0, txt_1, txt_2))
)
),
prefix, IF(n<0,"منفی ",""),
IF(
ISNUMBER(n),
prefix &
CONCAT(
STEP4(1, 12, " تریلیارد و ", " تریلیارد"),
STEP4(4, 9, " میلیارد و ", " میلیارد"),
STEP4(7, 6, " میلیون و ", " میلیون"),
STEP4(10, 3, " هزار و ", " هزار"),
IF(AND(--a = 0, n <> ""), "صفر", STEP4(13))
),
""
)
)
)
;
/*
FUNCTION NAME: KARTCHECK
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Check Correction of IRANIAN Bank Card Number
ARGS:
KartNumber: Card number with 16 lengh and text format
EXAMPLE:
=KARTCHECK("6104337963698616")
*/
KARTCHECK=LAMBDA(KartNumber,
LET(
s,SEQUENCE(8,,,2),
z,SEQUENCE(8,,2,2),
m,MID(KartNumber,s,1),
n,--MID(KartNumber,z,1),
i,IF(m*2>9,(m*2)-9,m*2),
f,SUMPRODUCT(i)+SUMPRODUCT(n),
IF(MOD(f,10),"Wrong","Correct")))
;
/*
FUNCTION NAME: MELICHECK
VERSION:1.0
AUTHOR: Mahmoud Baniasadi
WEB: www.arshad-hesabdar.ir
INSTAGRAM: @SoftwareTrain
DESCRIPTION: Check Correction of Iranian Meli Code or National Code of copmanies
ARGS:
NationalOrMelliCode: Card number with 16 lengh and text format
EXAMPLE:
=MELICHECK("0642245564")
=MELICHECK("10103021031")
*/
MELICHECK=
LAMBDA(NationalOrMelliCode,
LET(
l,LEN(NationalOrMelliCode),
c,SEQUENCE(l),
m,MOD(SUMPRODUCT(1*MID(NationalOrMelliCode,c,1),{10;9;8;7;6;5;4;3;2;0}),11),
n,MOD(SUMPRODUCT(1*MID(NationalOrMelliCode,c,1),{29;27;23;19;17;29;27;23;19;247;0})+460,11),
r,--RIGHT(NationalOrMelliCode),
i,ISBLANK(NationalOrMelliCode),
IF(i,"",IF(l=10,
IF(OR(AND(m<2,r=m),(11-m)=r),"Correct Meli","Wrong Meli"),
IF(l=11,IF(IF(n=10,0,n)=r,"Correct National ID","Wrong National ID"),"Wrong length")))))
;
//WynHopkins functions
//ListOfTextMatches------------------
// Select a word to look for and a column to look in
//outputs filtered list containing that text
ListOfTextMatches=
LAMBDA(
SelectTextToLookFor,
SelectRangeToLookIn,
LET(
FlagMatch,
ISNUMBER(
SEARCH( SelectTextToLookFor,SelectRangeToLookIn)
),
FILTER(SelectRangeToLookIn,FlagMatch,"Not Found")
)
)
;
// KeepOddItems----------------------------------
// Highlight a range of numbers and it FILTERS for the ODD numbers
//note the +0 forces the range to spill
KeepOddItems =
LAMBDA(SelectList,
LET(
FlagOdd, ISODD(SelectList+0),
_Result,FILTER(SelectList,FlagOdd,"No Odd Numbers"),
_Result)
)
// ErrorRowNumbers -----------------------------------------
// Return the row references of a column of values that contain errors
// Result is presented as commma separated list
ErrorRowNumbers =
LAMBDA( CellRange,
LET(
_RowNumbers, ROW(CellRange ),
_ERRORFlags, ISERROR(CellRange),
_FilteredList, FILTER( _RowNumbers, _ERRORFlags,"No Errors"),
TEXTJOIN(", ",,_FilteredList)
));
//DuplicatesCheck--------------------------
// Highlight a range of cells to get a statement of whether duplicates are present
DuplicatesCheck
=LAMBDA( RangeOfCells,
IF(
MAX( COUNTIFS( RangeOfCells, RangeOfCells ) )-1
<>0,
"Duplicates Exist",
"No Duplicates"
)
)
;
//DuplicatesListFromColumn---------------------------
// Text Joined list of duplicates in a selected column
DuplicatesListFromColumn
=LAMBDA(ColumnRange,
TEXTJOIN(
", ",,
SORT(
UNIQUE(
FILTER( ColumnRange,COUNTIFS( ColumnRange, ColumnRange ) > 1,0)
)
)
)
)
;
/*
FUNCTION NAME: APPENDCOLS
DESCRIPTION: Appends two arrays column wise
ARGS:
array1: The array to append columns from array2 to
array2: The array to append to array1
EXAMPLE:
=APPENDCOLS(SEQUENCE(10,2),SEQUENCE(10,2,11))
*/
APPENDCOLS =LAMBDA(array1, array2,
LET(
//name definitions
array1Rows, ROWS(array1),
array1Cols, COLUMNS(array1),
array2Rows, ROWS(array2),
array2Cols, COLUMNS(array2),
rowLen, MAX(array1Rows, array2Rows),
colLen, array1Cols + array2Cols,
newArray, SEQUENCE(rowLen, colLen),
colIndex, MOD(newArray - 1, colLen) + 1,
rowIndex, 1 + ((newArray - colIndex) / colLen),
//create the combined array
resultArray, IF(
colIndex > array1Cols,
INDEX(array2, rowIndex, colIndex - array1Cols),
INDEX(array1, rowIndex, colIndex)
),
//return the resultArray
resultArray
)
);
/*
FUNCTION NAME: APPENDROWS
DESCRIPTION: Appends two arrays row-wise
ARGS:
array1: The array to append rows from array2 to
array2: The array to append to array1
EXAMPLE:
=APPENDROWS(SEQUENCE(10), SEQUENCE(10, 1, 11))
*/
APPENDROWS =LAMBDA(array1, array2,
LET(
array1Rows, ROWS(array1),
colIndex, SEQUENCE(, MAX(COLUMNS(array1), COLUMNS(array2))),
rowIndex1, SEQUENCE(array1Rows + ROWS(array2)),
rowIndex2, rowIndex1 - array1Rows,
IF(
rowIndex2 >= 1,
INDEX(array2, rowIndex2, colIndex),
INDEX(array1, rowIndex1, colIndex)
)
)
);
/*
FUNCTION NAME: COUNTWORDS
DESCRIPTION: Counts the number of words in a text string
ARGS:
text: The text to string to count words
EXAMPLE:
=COUNTWORDS("The quick brown fox jumps over the lazy dog")
*/
COUNTWORDS =LAMBDA(text,
LET(
Trimmedtext, TRIM(text),
TrimmedtextLength, LEN(Trimmedtext),
WhiteSpaceSubstitution, SUBSTITUTE(Trimmedtext, " ", ""),
TextLengthNoSpaces, LEN(WhiteSpaceSubstitution),
TrimmedtextLength - TextLengthNoSpaces + 1
)
);
/*
FUNCTION NAME: TEXTREVERSE
DESCRIPTION: Reverses a text string
ARGS:
string: The text string to reverse
EXAMPLE:
=TEXTREVERSE("The quick brown fox jumps over the lazy dog")
*/
TEXTREVERSE =LAMBDA(string,
LET(
stringLength, LEN(string),
substring, RIGHT(string, stringLength - 1),
firstChar, LEFT(string, 1),
IF(LEN(string) = 0, string, TEXTREVERSE(substring) & firstChar)
)
);
/*
FUNCTION NAME: IFBLANK
DESCRIPTION: Checks if a value is blank and returns value_if_blank if it is
ARGS:
value: The value to check if it's blank
value_if_blank: The value to return if a blank value is found
EXAMPLE:
=IFBLANK(,"blankVal")
*/
IFBLANK =LAMBDA(value, value_if_blank, IF(ISBLANK(value),value_if_blank,value));
/*
FUNCTION NAME: DROPCOL
DESCRIPTION: Drops a column from an array
ARGS:
array: The array to drop a column from
column: The index of the column to drop
EXAMPLE:
=DROPCOL(SEQUENCE(10,3),3)
*/
DROPCOL =LAMBDA(array, column,
MAKEARRAY(
ROWS(array),
COLUMNS(array) -1,
LAMBDA(i, j, INDEX(array, i, IF(j <column, j, j+1)))
));
/*
FUNCTION NAME: PRODUCTIF
DESCRIPTION: Multiplies all values given that they pass a condition
ARGS:
values: The values to multiply given the met condition
condition: A LAMBDA which takes one argument (a value) and should return a boolean
if the condition is met
EXAMPLE:
=PRODUCTIF(SEQUENCE(5), LAMBDA(value, IF(value>3, TRUE, FALSE)))
*/
PRODUCTIF =LAMBDA(values, condition,
REDUCE(1, values, LAMBDA(a, b,
IF(condition(b), a*b, a)
)
));
/*
FUNCTION NAME: CHOOSERAND
DESCRIPTION: Returns an aray of random values from another array
ARGS:
array: The values to choose from
[rows]: The number of rows to return, if omitted returns one value
EXAMPLE:
=CHOOSERAND(SEQUENCE(50))
*/
CHOOSERAND =LAMBDA(array, [rows],
MAKEARRAY(IFOMITTED(rows, 1), 1, LAMBDA(a, b,
LET(arrayLen, COUNTA(array),
randIndex, RANDBETWEEN(1, arrayLen),
value, INDEX(array, randIndex),
value
)
)));
/*
FUNCTION NAME: IFOMITTED
DESCRIPTION: Checks if an optional value is omitted and returns value_if_omitted if it is
ARGS:
value: The value to check if it is omitted
value_if_omitted: The value to return if a the value is omitted
EXAMPLE:
=IFOMITTED(,"omitted")
*/
IFOMITTED =LAMBDA(value, value_if_omitted,
IF(ISOMITTED(value),
value_if_omitted, value
));
/*
Author: andrewcharlesmoss
UPDATE 13/02/2022
Added columns argument and made each optional
*/
// Returns an array of unique random numbers
RANDUNIQUE =
LAMBDA([rows],[columns],[min],[max],
INDEX(
SORTBY(
SEQUENCE(max+1-min,,min),
RANDARRAY(max+1-min)
),
SEQUENCE(rows,columns)
)
)
;
// Unpivot columns
Unpivot =
LAMBDA(array,
HSTACK(
TEXTSPLIT(
TEXTJOIN("|",1,
DROP(
TAKE(array,,1)&
"_"&
TAKE(array,1)
,1,1
)
),
"_","|"),
TOCOL(DROP(array,1,1))
)
)
;
@Farhadi26
Copy link

Farhadi26 commented Feb 16, 2022

عالی استاد
علاوه بر سه بار دیدن یوتیوب تمام فرمولا هم چک کردم، دستمریزاد.💐
همونی که نفر چهارم کلیپ رو دیدمش😉

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment