Convert Number to Persian Text

afshin · September 9, 2022, 7:18am

Hello.

I have a complicated function that must convert numbers to Persian (Farsi) text.
For example when I enter 123 this function must convert it to:
صد و بیست و سه

but when I use this function, it happens Error 501.
This function works fine in MS-Excel.

Function is:

=IF(NOT(ISNUMBER(A2)),"",CHOOSE(MID(TEXT(A2,"000000000000000"),1,1)+1,,"صد","دویست","سیصد","چهارصد","پانصد","ششصد","هفتصد","هشتصد","نهصد")
&IF(--MID(TEXT(A2,"000000000000000"),1,1)=0,,IF(AND(--MID(TEXT(A2,"000000000000000"),2,1)=0,--MID(TEXT(A2,"000000000000000"),3,1)=0),," و "))
&CHOOSE(MID(TEXT(A2,"000000000000000"),2,1)+1,,,"بیست","سی","چهل","پنجاه","شصت","هفتاد","هشتاد","نود")
&IF(OR(--MID(TEXT(A2,"000000000000000"),2,1)=0,--MID(TEXT(A2,"000000000000000"),2,1)=1,--MID(TEXT(A2,"000000000000000"),3,1)=0),," و ")
&IF(--MID(TEXT(A2,"000000000000000"),2,1)<>1,CHOOSE(MID(TEXT(A2,"000000000000000"),3,1)+1,,"یک","دو","سه","چهار","پنج","شش","هفت","هشت","نه"),CHOOSE(MID(TEXT(A2,"000000000000000"),3,1)+1,"ده","یازده","دوازده","سیزده","چهارده","پانزده","شانزده","هفده","هجده","نوزده"))
&IF(--MID(TEXT(A2,"000000000000000"),1,3)=0,,IF(--RIGHT(TEXT(A2,"000000000000000"),12)>0," تریلیارد و "," تریلیارد"))

&CHOOSE(MID(TEXT(A2,"000000000000000"),4,1)+1,,"صد","دویست","سیصد","چهارصد","پانصد","ششصد","هفتصد","هشتصد","نهصد")
&IF(--MID(TEXT(A2,"000000000000000"),4,1)=0,,IF(AND(--MID(TEXT(A2,"000000000000000"),5,1)=0,--MID(TEXT(A2,"000000000000000"),6,1)=0),," و "))
&CHOOSE(MID(TEXT(A2,"000000000000000"),5,1)+1,,,"بیست","سی","چهل","پنجاه","شصت","هفتاد","هشتاد","نود")
&IF(OR(--MID(TEXT(A2,"000000000000000"),5,1)=0,--MID(TEXT(A2,"000000000000000"),5,1)=1,--MID(TEXT(A2,"000000000000000"),6,1)=0),," و ")
&IF(--MID(TEXT(A2,"000000000000000"),5,1)<>1,CHOOSE(MID(TEXT(A2,"000000000000000"),6,1)+1,,"یک","دو","سه","چهار","پنج","شش","هفت","هشت","نه"),CHOOSE(MID(TEXT(A2,"000000000000000"),6,1)+1,"ده","یازده","دوازده","سیزده","چهارده","پانزده","شانزده","هفده","هجده","نوزده"))
&IF(--MID(TEXT(A2,"000000000000000"),4,3)=0,,IF(--RIGHT(TEXT(A2,"000000000000000"),9)>0," میلیارد و "," میلیارد"))

&CHOOSE(MID(TEXT(A2,"000000000000000"),7,1)+1,,"صد","دویست","سیصد","چهارصد","پانصد","ششصد","هفتصد","هشتصد","نهصد")
&IF(--MID(TEXT(A2,"000000000000000"),7,1)=0,,IF(AND(--MID(TEXT(A2,"000000000000000"),8,1)=0,--MID(TEXT(A2,"000000000000000"),9,1)=0),," و "))
&CHOOSE(MID(TEXT(A2,"000000000000000"),8,1)+1,,,"بیست","سی","چهل","پنجاه","شصت","هفتاد","هشتاد","نود")
&IF(OR(--MID(TEXT(A2,"000000000000000"),8,1)=0,--MID(TEXT(A2,"000000000000000"),8,1)=1,--MID(TEXT(A2,"000000000000000"),9,1)=0),," و ")
&IF(--MID(TEXT(A2,"000000000000000"),8,1)<>1,CHOOSE(MID(TEXT(A2,"000000000000000"),9,1)+1,,"یک","دو","سه","چهار","پنج","شش","هفت","هشت","نه"),CHOOSE(MID(TEXT(A2,"000000000000000"),9,1)+1,"ده","یازده","دوازده","سیزده","چهارده","پانزده","شانزده","هفده","هجده","نوزده"))
&IF(--MID(TEXT(A2,"000000000000000"),7,3)=0,,IF(--RIGHT(TEXT(A2,"000000000000000"),6)>0," میلیون و "," میلیون"))

&CHOOSE(MID(TEXT(A2,"000000000000000"),10,1)+1,,"صد","دویست","سیصد","چهارصد","پانصد","ششصد","هفتصد","هشتصد","نهصد")
&IF(--MID(TEXT(A2,"000000000000000"),10,1)=0,,IF(AND(--MID(TEXT(A2,"000000000000000"),11,1)=0,--MID(TEXT(A2,"000000000000000"),12,1)=0),," و "))
&CHOOSE(MID(TEXT(A2,"000000000000000"),11,1)+1,,,"بیست","سی","چهل","پنجاه","شصت","هفتاد","هشتاد","نود")
&IF(OR(--MID(TEXT(A2,"000000000000000"),11,1)=0,--MID(TEXT(A2,"000000000000000"),11,1)=1,--MID(TEXT(A2,"000000000000000"),12,1)=0),," و ")
&IF(--MID(TEXT(A2,"000000000000000"),11,1)<>1,CHOOSE(MID(TEXT(A2,"000000000000000"),12,1)+1,,"یک","دو","سه","چهار","پنج","شش","هفت","هشت","نه"),CHOOSE(MID(TEXT(A2,"000000000000000"),12,1)+1,"ده","یازده","دوازده","سیزده","چهارده","پانزده","شانزده","هفده","هجده","نوزده"))
&IF(--MID(TEXT(A2,"000000000000000"),10,3)=0,,IF(--RIGHT(TEXT(A2,"000000000000000"),3)>0," هزار و "," هزار"))

&IF(AND(--TEXT(A2,"000000000000000")=0,A2<>""),"صفر",CHOOSE(MID(TEXT(A2,"000000000000000"),13,1)+1,,"صد","دویست","سیصد","چهارصد","پانصد","ششصد","هفتصد","هشتصد","نهصد")
&IF(--MID(TEXT(A2,"000000000000000"),13,1)=0,,IF(AND(--MID(TEXT(A2,"000000000000000"),14,1)=0,--MID(TEXT(A2,"000000000000000"),15,1)=0),," و "))
&CHOOSE(MID(TEXT(A2,"000000000000000"),14,1)+1,,,"بیست","سی","چهل","پنجاه","شصت","هفتاد","هشتاد","نود")
&IF(OR(--MID(TEXT(A2,"000000000000000"),14,1)=0,--MID(TEXT(A2,"000000000000000"),14,1)=1,--MID(TEXT(A2,"000000000000000"),15,1)=0),," و ")
&IF(--MID(TEXT(A2,"000000000000000"),14,1)<>1,CHOOSE(MID(TEXT(A2,"000000000000000"),15,1)+1,,"یک","دو","سه","چهار","پنج","شش","هفت","هشت","نه"),CHOOSE(MID(TEXT(A2,"000000000000000"),15,1)+1,"ده","یازده","دوازده","سیزده","چهارده","پانزده","شانزده","هفده","هجده","نوزده"))))

mikekaganski · September 9, 2022, 7:41am

Note that you use some localized syntax in your formula (e.g., using commas for argument separators), not an international one using semicolons. Depending on the settings in your LibreOffice, that could give some unexpected results.

This version of your formula, created by simple replacement of , with ;, works for me:

=IF(NOT(ISNUMBER(A2));"";CHOOSE(MID(TEXT(A2;"000000000000000");1;1)+1;;"صد";"دویست";"سیصد";"چهارصد";"پانصد";"ششصد";"هفتصد";"هشتصد";"نهصد")
&IF(--MID(TEXT(A2;"000000000000000");1;1)=0;;IF(AND(--MID(TEXT(A2;"000000000000000");2;1)=0;--MID(TEXT(A2;"000000000000000");3;1)=0);;" و "))
&CHOOSE(MID(TEXT(A2;"000000000000000");2;1)+1;;;"بیست";"سی";"چهل";"پنجاه";"شصت";"هفتاد";"هشتاد";"نود")
&IF(OR(--MID(TEXT(A2;"000000000000000");2;1)=0;--MID(TEXT(A2;"000000000000000");2;1)=1;--MID(TEXT(A2;"000000000000000");3;1)=0);;" و ")
&IF(--MID(TEXT(A2;"000000000000000");2;1)<>1;CHOOSE(MID(TEXT(A2;"000000000000000");3;1)+1;;"یک";"دو";"سه";"چهار";"پنج";"شش";"هفت";"هشت";"نه");CHOOSE(MID(TEXT(A2;"000000000000000");3;1)+1;"ده";"یازده";"دوازده";"سیزده";"چهارده";"پانزده";"شانزده";"هفده";"هجده";"نوزده"))
&IF(--MID(TEXT(A2;"000000000000000");1;3)=0;;IF(--RIGHT(TEXT(A2;"000000000000000");12)>0;" تریلیارد و ";" تریلیارد"))

&CHOOSE(MID(TEXT(A2;"000000000000000");4;1)+1;;"صد";"دویست";"سیصد";"چهارصد";"پانصد";"ششصد";"هفتصد";"هشتصد";"نهصد")
&IF(--MID(TEXT(A2;"000000000000000");4;1)=0;;IF(AND(--MID(TEXT(A2;"000000000000000");5;1)=0;--MID(TEXT(A2;"000000000000000");6;1)=0);;" و "))
&CHOOSE(MID(TEXT(A2;"000000000000000");5;1)+1;;;"بیست";"سی";"چهل";"پنجاه";"شصت";"هفتاد";"هشتاد";"نود")
&IF(OR(--MID(TEXT(A2;"000000000000000");5;1)=0;--MID(TEXT(A2;"000000000000000");5;1)=1;--MID(TEXT(A2;"000000000000000");6;1)=0);;" و ")
&IF(--MID(TEXT(A2;"000000000000000");5;1)<>1;CHOOSE(MID(TEXT(A2;"000000000000000");6;1)+1;;"یک";"دو";"سه";"چهار";"پنج";"شش";"هفت";"هشت";"نه");CHOOSE(MID(TEXT(A2;"000000000000000");6;1)+1;"ده";"یازده";"دوازده";"سیزده";"چهارده";"پانزده";"شانزده";"هفده";"هجده";"نوزده"))
&IF(--MID(TEXT(A2;"000000000000000");4;3)=0;;IF(--RIGHT(TEXT(A2;"000000000000000");9)>0;" میلیارد و ";" میلیارد"))

&CHOOSE(MID(TEXT(A2;"000000000000000");7;1)+1;;"صد";"دویست";"سیصد";"چهارصد";"پانصد";"ششصد";"هفتصد";"هشتصد";"نهصد")
&IF(--MID(TEXT(A2;"000000000000000");7;1)=0;;IF(AND(--MID(TEXT(A2;"000000000000000");8;1)=0;--MID(TEXT(A2;"000000000000000");9;1)=0);;" و "))
&CHOOSE(MID(TEXT(A2;"000000000000000");8;1)+1;;;"بیست";"سی";"چهل";"پنجاه";"شصت";"هفتاد";"هشتاد";"نود")
&IF(OR(--MID(TEXT(A2;"000000000000000");8;1)=0;--MID(TEXT(A2;"000000000000000");8;1)=1;--MID(TEXT(A2;"000000000000000");9;1)=0);;" و ")
&IF(--MID(TEXT(A2;"000000000000000");8;1)<>1;CHOOSE(MID(TEXT(A2;"000000000000000");9;1)+1;;"یک";"دو";"سه";"چهار";"پنج";"شش";"هفت";"هشت";"نه");CHOOSE(MID(TEXT(A2;"000000000000000");9;1)+1;"ده";"یازده";"دوازده";"سیزده";"چهارده";"پانزده";"شانزده";"هفده";"هجده";"نوزده"))
&IF(--MID(TEXT(A2;"000000000000000");7;3)=0;;IF(--RIGHT(TEXT(A2;"000000000000000");6)>0;" میلیون و ";" میلیون"))

&CHOOSE(MID(TEXT(A2;"000000000000000");10;1)+1;;"صد";"دویست";"سیصد";"چهارصد";"پانصد";"ششصد";"هفتصد";"هشتصد";"نهصد")
&IF(--MID(TEXT(A2;"000000000000000");10;1)=0;;IF(AND(--MID(TEXT(A2;"000000000000000");11;1)=0;--MID(TEXT(A2;"000000000000000");12;1)=0);;" و "))
&CHOOSE(MID(TEXT(A2;"000000000000000");11;1)+1;;;"بیست";"سی";"چهل";"پنجاه";"شصت";"هفتاد";"هشتاد";"نود")
&IF(OR(--MID(TEXT(A2;"000000000000000");11;1)=0;--MID(TEXT(A2;"000000000000000");11;1)=1;--MID(TEXT(A2;"000000000000000");12;1)=0);;" و ")
&IF(--MID(TEXT(A2;"000000000000000");11;1)<>1;CHOOSE(MID(TEXT(A2;"000000000000000");12;1)+1;;"یک";"دو";"سه";"چهار";"پنج";"شش";"هفت";"هشت";"نه");CHOOSE(MID(TEXT(A2;"000000000000000");12;1)+1;"ده";"یازده";"دوازده";"سیزده";"چهارده";"پانزده";"شانزده";"هفده";"هجده";"نوزده"))
&IF(--MID(TEXT(A2;"000000000000000");10;3)=0;;IF(--RIGHT(TEXT(A2;"000000000000000");3)>0;" هزار و ";" هزار"))

&IF(AND(--TEXT(A2;"000000000000000")=0;A2<>"");"صفر";CHOOSE(MID(TEXT(A2;"000000000000000");13;1)+1;;"صد";"دویست";"سیصد";"چهارصد";"پانصد";"ششصد";"هفتصد";"هشتصد";"نهصد")
&IF(--MID(TEXT(A2;"000000000000000");13;1)=0;;IF(AND(--MID(TEXT(A2;"000000000000000");14;1)=0;--MID(TEXT(A2;"000000000000000");15;1)=0);;" و "))
&CHOOSE(MID(TEXT(A2;"000000000000000");14;1)+1;;;"بیست";"سی";"چهل";"پنجاه";"شصت";"هفتاد";"هشتاد";"نود")
&IF(OR(--MID(TEXT(A2;"000000000000000");14;1)=0;--MID(TEXT(A2;"000000000000000");14;1)=1;--MID(TEXT(A2;"000000000000000");15;1)=0);;" و ")
&IF(--MID(TEXT(A2;"000000000000000");14;1)<>1;CHOOSE(MID(TEXT(A2;"000000000000000");15;1)+1;;"یک";"دو";"سه";"چهار";"پنج";"شش";"هفت";"هشت";"نه");CHOOSE(MID(TEXT(A2;"000000000000000");15;1)+1;"ده";"یازده";"دوازده";"سیزده";"چهارده";"پانزده";"شانزده";"هفده";"هجده";"نوزده"))))

mikekaganski · September 9, 2022, 7:33am

Since version 6.1, there is a new “spell out” NatNum modifier, which should allow you to have it natively, without any effort. You could just use [NatNum12]0 format string to get the wanted result, either as a cell number format, or as an argument to TEXT spreadsheet function.

However, this function relies on the respective language data present in the Numbertext library; and it looks that no one from Persian community took an effort to provide that language information there. You could be the one who does that job, benefitting from that yourself in a following LibreOffice release that incorporates your contribution, and at the same time, making life easier for all your fellow users who need this.