Changing Number to Text
I currently using this code in Google Sheet to replace my numbers(Currency) to text. But its showing error for this part
IF(IFERROR(QUERY({Y:Y},"where Col1 Contains '.'"),)<>"", " and "®EXEXTRACT(TO_TEXT(Y:Y), "\.(.+)")&" cents", ))," ", " ")
What should I change to make it work properly?
My full code below:
=ARRAYFORMULA(IFS(ROW(AQ:AQ)=1,"WageWords",A:A="","",TRUE,REGEXREPLACE(IF(OR(LEN(FLOOR(Y:Y,1))>=13,FLOOR(Y:Y,1)<=0),"", PROPER(SUBSTITUTE(CONCATENATE(CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")), IF(Y:Y>=10^9," billion ",""), CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),4,3))>0," million ",""),CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),7,3))," thousand ",""),CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred "), CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety"), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"), IF(VALUE(MID(TEXT(INT(Y:Y), REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(Y:Y), REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," "))& IF(IFERROR(QUERY({Y:Y},"where Col1 Contains '.'"),)<>"", " and "®EXEXTRACT(TO_TEXT(Y:Y), "\.(.+)")&" cents", ))," ", " ")))
use:
=ARRAYFORMULA(IF(Y:Y="",,REGEXREPLACE(IF((LEN(FLOOR(Y:Y,1))>=13)+(FLOOR(Y:Y,1)<=0),"Out of range",
PROPER(SUBSTITUTE((CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),1,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred ")&
CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),2,1)+1,"",CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),3,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),2,1))>1,CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),3,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),2,1))=0,CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),3,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),""))&
IF(Y:Y>=10^9," billion ","")&
CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),4,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred ")&
CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),5,1)+1,"",CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),6,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),5,1))>1,CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),6,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),5,1))=0,CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),6,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),""))&
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),4,3))>0," million ","")&CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),7,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred ")&
CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),8,1)+1,"",CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),9,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),8,1))>1,CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),9,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),8,1))=0,CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),9,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),""))&
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),7,3))," thousand ","")&CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),10,1)+1,"","one hundred ","two hundred ","three hundred ","four hundred ","five hundred ","six hundred ","seven hundred ","eight hundred ","nine hundred ")&
CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),11,1)+1,"",CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),12,1)+1,"ten","eleven","twelve","thirteen","fourteen","fifteen","sixteen","seventeen","eighteen","nineteen"),"twenty","thirty","forty","fifty","sixty","seventy","eighty","ninety")&
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),11,1))>1,CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),12,1)+1,"","-one","-two","-three","-four","-five","-six","-seven","-eight","-nine"),
IF(VALUE(MID(TEXT(INT(Y:Y),
REPT(0,12)),11,1))=0,CHOOSE(MID(TEXT(INT(Y:Y),
REPT(0,12)),12,1)+1,"","one","two","three","four","five","six","seven","eight","nine"),"")))," "," "))&
IFERROR(IF(REGEXMATCH(TO_TEXT(Y:Y), "\."),
" and "®EXEXTRACT(TO_TEXT(Y:Y), "\.(.+)")&" cents", )))," ", " ")))
- How do you add UI inside cells in a google spreadsheet using app script?
- How can I force data input into cells in Google Sheets to be in a desired date format?
- How do I find a partial match from a data table to an individual cell?
- Highlighting non-first instance duplicates of words and counting highlighted cells
- Schedule View: INDEX MATCH from table and return multiple cells into one
- How to Show/Hide rows in google sheets using a script?
- Popping a Modal Dialog when changing the drop down entry in Google Sheets
- Transfer background color from cell range to drop down options
- How can I match a smart chip using SUMIF?
- List Google drive folder contents to google sheets with only new files
- Trying to figure out why my GoogleSheets Date formula is not working
- Count if name matches and outcome is complete
- Google Sheets Row Number of last row in a defined table
- How can I use INDEX and MATCH along with conditions to filter data into different tables?
- Google Sheets formula to remove all punctuation excluding period (.)
- Formula to find missing serial numbers
- Google Sheet Vlookup Multiple Criteria
- Copy Data from one column and past at the end of another column
- Query formula not grouping properly
- Comparing text fields that contain dates in Google Sheets
- Signature Pad (Canvas) to Google Sheets
- How to use the cell number instead of its value in a Filter + Importrange
- regexreplace multiple columns for football data
- I want to use a macro to check a value in a sheet and return one of two values
- How can I remove consecutive duplicates from a range in Google Sheets?
- Using FILTER with multiple conditions on Google Sheet
- How to union ranges in google spreadsheets
- Google Sheets Dynamic Dropdown List Dependent with multiple rows
- Formula for filtering list based off of key with multiple criteria in Google Sheets
- Conditional Dropdowns in Google Sheets Based on Multiple Criteria