Blog Post

Excel Blog
3 MIN READ

Announcing LET

Chris_Gross's avatar
Chris_Gross
Icon for Microsoft rankMicrosoft
Mar 17, 2020
Have you ever had to repeat the same expression multiple times within a formula, created a mega formula or wished that you had a way to reuse portions of your formula for easier consumption? With the...
Updated Jul 23, 2020
Version 10.0
blog
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 19, 2020

PeterBartholomew1 , excellent!

Let me combine all together

=LET(
   Denom, {" Million, ";" Thousand ";" Dollars ";" Cents"},
   Nums, {"","One","Two","Three","Four","Five","Six","Seven","Eight"," Nine"},
   Teens, {"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"},
   Tens, {"","Ten","Twenty"," Thirty"," Forty","Fifty","Sixty"," Seventy","Eighty","Ninety"},
   grp, {0;1;2;3},
   LET(
      N, SUBSTITUTE( TEXT( A1, REPT(0,9)&".00" ),".","0"),
      H, VALUE( MID( N, 3*grp+1, 1) ),
      T, VALUE( MID( N, 3*grp+2, 1) ),
      U, VALUE( MID( N, 3*grp+3, 1) ),
      H.txt, IF( H, INDEX( Nums, H+1 ) & " Hundred ", "" ),
      T.txt,  IF( T>1, INDEX( Tens, T+1 ) & IF( U>0, "-", "" ), " " ),
      U.txt, IF( (T+U),  IF( T=1, INDEX( Teens, U+1 ), INDEX(Nums, U+1 ) ) ),
   CONCAT( IF( H+T+U, H.txt & T.txt & U.txt & Denom, "" ) )
   )
)