Search code examples
excelexcel-2013

Spell number in excel 2013 using 2 rows

I'm trying to contain 2 rows whenever I use spell number in excel 2013, I wanted to get the second output. Any idea?

enter image description here

Note: SpellNumber is a Microsoft provided User Defined Function https://support.microsoft.com/en-us/help/213360/how-to-convert-a-numeric-value-into-english-words-in-excel

Solution

  • Splitting the Number

    Top Row: =SpellNumber((TRUNC(J7/100)*100))

    Second Row: =SpellNumber(J7 - (TRUNC(J7/100)*100))

    (where J7 is the cell of your numerical value.)

    That will split the number apart, but spell number will still spell out to the cent for the first row like this...

    Ten Thousand One Hundred Pesos No Centavos

    Splitting the Text

    To only get the text before the "Pesos No Centavos" or "Dollars and No Cents" portion use something like this...

    =LEFT(SpellNumber((TRUNC(J7/100)*100)),FIND("Pesos",SpellNumber(TRUNC(J7/100)*100))-2)

    Or this might be more currency agnostic...

    =LEFT(SpellNumber((TRUNC(J7/100)*100)),FIND("Hundred",SpellNumber(TRUNC(J7/100)*100))+6)

    If you're splitting on the text "Hundred", then you could get by with the top equation just being this...

    =LEFT(SpellNumber(J7),FIND("Hundred",SpellNumber(J7))+6)

    For more information about splitting text in Excel, see here: https://support.office.com/en-us/article/Split-text-into-different-columns-with-functions-49ec57f9-3d5a-44b2-82da-50dded6e4a68

    Note: SpellNumber is a Microsoft provided User Defined Function: https://support.microsoft.com/en-us/help/213360/how-to-convert-a-numeric-value-into-english-words-in-excel