You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Converting numbers to text.

Can anyone please advise if there is a formula to automatically convert number to text in Numbers app. I want to make an invoice in which the invoice value in numbers is automatically converted to text in another cell.

MacBook Pro 15″, macOS 11.4

Posted on Jun 20, 2021 10:57 AM

Reply
Question marked as Top-ranking reply

Posted on Jun 21, 2021 11:24 PM

Do you man literally? as in convert "12345" to "Twelve thousand three hundred and forty five"


Seems like an odd way to reference invoice numbers, but hey, I'm not an accountant.


I have an AppleScript that I wrote that converts any arbitrary number to text - literally:


numToText("1234567890012345678901234567890") => "One Nonillion Two Hundred and Thirty Four Octillion Five Hundred and Sixty Seven Septillion Eight Hundred and Ninety  Sextillion Twelve Quintrillion Three Hundred and Fourty Five Quadrillion Six Hundred and Seventy Eight Trillion Nine Hundred and One Billion Two Hundred and Thirty Four Million Five Hundred and Sixty Seven Thousand Eight Hundred and Ninety"


I can post it here if you like. However, if you just want to treat the result as a text object rather than a numerical object, there are far simpler ways to do that :)

4 replies
Question marked as Top-ranking reply

Jun 21, 2021 11:24 PM in response to ashhydind

Do you man literally? as in convert "12345" to "Twelve thousand three hundred and forty five"


Seems like an odd way to reference invoice numbers, but hey, I'm not an accountant.


I have an AppleScript that I wrote that converts any arbitrary number to text - literally:


numToText("1234567890012345678901234567890") => "One Nonillion Two Hundred and Thirty Four Octillion Five Hundred and Sixty Seven Septillion Eight Hundred and Ninety  Sextillion Twelve Quintrillion Three Hundred and Fourty Five Quadrillion Six Hundred and Seventy Eight Trillion Nine Hundred and One Billion Two Hundred and Thirty Four Million Five Hundred and Sixty Seven Thousand Eight Hundred and Ninety"


I can post it here if you like. However, if you just want to treat the result as a text object rather than a numerical object, there are far simpler ways to do that :)

Jun 20, 2021 5:09 PM in response to ashhydind

There is no formula to do that. It can be done by parsing the number into columns then doing lookups on those columns to replace the numbers with text. I can post a Numbers document that goes up to 999.99. Th dollars are converted to text The cents are not changed to text, they are stated as a fraction.


The general idea is like this:

543.45 = 500 40 3 and 45/100 = five hundred forty three and 45/100

19.43 = 19 and 43/100 = nineteen and 43/100


You could follow the same logic and expand it to thousands and ten thousands or higher.

Jun 22, 2021 11:31 AM in response to ashhydind

I did my best to rework the spreadsheet I had (whoever it came from) to make the conversion a single-cell formula versus a multi-column calculation. I also increased the range so it would give results in the millions and also negatives. It rounds to two decimal places. It requires a table of words (as did the multi-column version) but the formula itself is a single cell.


I make no claims to the accuracy of the formula. I recommend testing it more thoroughly than I did, just to make sure it doesn't mess any numbers up. Sheet 2 of the attached document was for testing. Let me know if it has any problems.


There is probably more than one way to do this, with one or more of them being simpler than what I came up with, but this is what I have.



The formula in column B is

=TRIM(IF(A<0,"Minus ","")&INDEX(Table 2::$A,TRUNC(ABS(A),−6)×10^−6+1)&" "&INDEX(Table 2::$B,TRUNC(ABS(A),−6)×10^−6+1)&" "&""&INDEX(Table 2::$A,MOD(TRUNC(ABS(A),−3),10^6)×10^−3+1)&" "&INDEX(Table 2::$C,MOD(TRUNC(ABS(A),−3),10^6)×10^−3+1)&" "&" "&INDEX(Table 2::$A,MOD(TRUNC(ABS(A),0),10^3)+1)&""&" "&IF(ABS(A)≥1," and ","")&IF(ABS(A)≠0,RIGHT("00"&MOD(ROUND(ABS(A),2),1)×10^+2,2)&"/100",""))


In the table of words, columns B and C are there to make my formula easier. They cover the cases of "zero million" and "zero thousand". I handle those with a simple INDEX function vs having to do IF statements to determine whether to include the word "million" or "thousand" in the string.


https://www.dropbox.com/s/cr38lhdm8hf36no/Number%20to%20words.numbers?dl=0


It is Sheet 1 of the linked spreadsheet. Copy/paste both tables from Sheet 1 at the same time into a blank sheet in your document and that should keep all the references and formulas intact.


To use it, select a cell in column B, Copy, then Paste it to the right of a cell somewhere else in your spreadsheet. The references will adjust and it will convert that cell.

Converting numbers to text.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.