convert number to its Name

[ChetanBhavsar]

Hello All,
Will you please help me to write a UDF for below query.

If a Cell A1 has a number then B1 will have its name. For Example. If A1 has 67 as number then B1 should display as SIX SEVEN and not Sixty Seven.
Please note i want to have a every digit to be display as its name and not by its position as tenth, hundredth, thousand etc

till now i have been doing a work around which is very lengthy and leading to frustration.
I am first dividing the number in A1 by 10 and then by using Text to Column wizard separating numbers after decimal and concatenating their Names to one cell

Any help in this will be appreciated.

Thanks,
Chetan

 

[GraH - Guido]

Hi ChetanBhavsar, I know you asked for UDF, but I wanted to let you know there is an alternative via Power Query too.
It depends on your Excel version, but if it is one of the more modern ones (2013/2016/2019/365), then you have it onboard.
Sample attached.

 

[ChetanBhavsar]

Hello GraH - Guido,
How did you do that.? How can i use Power Query. I have Office365.
Please guide me.

 

[GraH - Guido]

Hello GraH - Guido,
How did you do that? How can I use Power Query. I have Office365.

Hi,

That's like asking "How can I use Excel?" It's a bit too generic to answer. Power Query allows to do (almost any) data transformation you can imagine. You have it under the hood on the data section "Get & Transform" on your ribbon. Basically PQ is referred to as an ETL tool: one can extract data from a bunch of sources, it does transformations on that data an finally can load it in Excel in a table or a pivot. Many of the manipulations are do-able with some simple mouse clicks, making this a badass power tool. Tweaking the code comes later. It's maybe similar to recording a macro than learning how to read this code before finally being able to customize the code and make it more robust.

What I did here is making a table named Digits: this is used inside PQ to make the replacement lists: hence 1 is replaced by the word one, 2 by the word two and so on.


I also made a table filled with random numbers of variable length, called Numbers. Also this table is loaded in Power Query (data from table/range or even "From worksheet" if you already have the latest Excel).

First Query (on Digits).

1. Add a custom column and the formula {Number.ToText([Digit]),[Read As]}
   • Number.ToText converts the digit into text, since I will split the numbers in digits later and I only know how to do this with a text function.
   • the curly brackets { } around this formula tells PQ to treat this as a list. Later I can than simply use a List.ReplaceMatchingValues( ) function.
2. I then add a step MatchList[List]
   • MatchList refers to the name of the previous step. Note that all steps of the Power Query script get a name attributed by default. I always rename them.
   • The square brackets [ ] tell PQ I only want to extract the column called List. Doing so makes this single column appear as a list. So this becomes a list of lists. The second list is something like {"1" , "one"}. The digit and the replacement value. Mind the double quotes indicate the list members are to be treated as text.
   • the full list looks like { { "1", "one" } , {"2", "two" } , { "3", "three" } ,... {"0", "zero" } }
3. This query is loaded as connection only and I named the Query ReplaceList.

Second Query (on Numbers).

1. Also here I add a custom column, and in this case the formula is actually a nested Query. But you can do each step in an additional custom column. And in the final step you can get rid of these helper columns by deleting them inside the Query. The formula is this code.
   

   let
      Digits = Text.ToList(Number.ToText([numbers])),
      Replace = List.ReplaceMatchingItems(Digits, ReplaceList),
      String = Text.Combine(Replace, " ")
      in
      String

   • let is a key word for PQ indicating this is the beginning of a PQ
   • in is the keyword indication the PQ ends, and it is followed with the name of the step you want in your result. Most of the times this is your last step of the query, but it does not need to be.
   • Before the = you have the name of the step
   • Digits: Text.ToList(Number.ToText([numbers]))
     • Number.ToText converts the number to a text string
     • Text.To List creates a list of all digits of the string
   • Replace:List.ReplaceMatchingItems(Digits, ReplaceList)
     • List.ReplaceMatchingItems will take the list of Digits from the previous step and replace matching items. So a number like 754, became a text "754", became a list { "7", "5", "4" } and this list is transformed to again a list like { "seven", "five", "four" }
   • String: Text.Combine(Replace, " ")
     • Text.Combine will create a new string from all members inside the list. The space " " is the given delimiter.
     • The result is a string like "seven five four"
2. Load query as table in excel

Paste any new data inside the table Numbers, and press refresh on the result table. Magic happens .

To view the queries in the provided file: go the data ribbon, Queries and Connections section and toggle "Queries and Connections". They will appear on the side. Then select any query and click edit to open the Power Query window.


This might be a lot to take in, but I advise to start with baby steps and google some tutorials on Power Query. You won't regret it.

 

[navic]

Maybe with a formula.
The formula uses the helper data range in 'A2:B11', which is named "rng".

=IFERROR(VLOOKUP(MID($E2;1;1)*1;rng;2;FALSE);"")&" "&IFERROR(VLOOKUP(MID($E2;2;1)*1;rng;2;FALSE);"")&" "&IFERROR(VLOOKUP(MID($E2;3;1)*1;rng;2;FALSE);"")&" "&IFERROR(VLOOKUP(MID($E2;4;1)*1;rng;2;FALSE);"")&" "&IFERROR(VLOOKUP(MID($E2;5;1)*1;rng;2;FALSE);"")&" "&IFERROR(VLOOKUP(MID($E2;6;1)*1;rng;2;FALSE);"")&" "&IFERROR(VLOOKUP(MID($E2;7;1)*1;rng;2;FALSE);"")&" "&IFERROR(VLOOKUP(MID($E2;8;1)*1;rng;2;FALSE);"")

To make it easier to understand the formula above, we can write it like this.

=IFERROR(VLOOKUP(MID($E2;1;1)*1;rng;2;FALSE);"")
&" "&IFERROR(VLOOKUP(MID($E2;2;1)*1;rng;2;FALSE);"")
&" "&IFERROR(VLOOKUP(MID($E2;3;1)*1;rng;2;FALSE);"")
&" "&IFERROR(VLOOKUP(MID($E2;4;1)*1;rng;2;FALSE);"")
&" "&IFERROR(VLOOKUP(MID($E2;5;1)*1;rng;2;FALSE);"")
&" "&IFERROR(VLOOKUP(MID($E2;6;1)*1;rng;2;FALSE);"")
&" "&IFERROR(VLOOKUP(MID($E2;7;1)*1;rng;2;FALSE);"")
&" "&IFERROR(VLOOKUP(MID($E2;8;1)*1;rng;2;FALSE);"")

This formula is up to 8 digits. If you want to add more conditions then join this formula to the main formula.

&" "&IFERROR(VLOOKUP(MID($E2;9;1)*1;rng;2;FALSE);"")

The number 9 in the formula above means that it is for the last ninth digit.

 

[KidneyStone]

Hi Chetan,

This UDF worked when I tested.

Function NumberName(target As Range) As String
Dim ArrTxt
Dim x As Long
ArrTxt = Array("Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
For x = 1 To Len(target.Value2)
    If IsNumeric(Mid(target.Value2, x, 1)) Then
        NumberName = NumberName & " " & ArrTxt(Mid(target.Value2, x, 1))
    End If
Next x
End Function

 

[GraH - Guido]

Or formula
=TEXTJOIN(" ",TRUE,XLOOKUP(MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),$A$1:$A$10,$B$1:$B$10,,0))

 

[GraH - Guido]

Or without volatile indirect function and without help table. Still getting used to have Dynamic Array functions on board.

=TEXTJOIN(" ",TRUE,XLOOKUP(MID(C2,SEQUENCE(LEN(C2)),1)+0,{1,2,3,4,5,6,7,8,9,0},{"one","two","three","four","five","six","seven","eight","nine","zero"},,0))

Yet, originally UDF was requested, and KidneyStone delivered just that. Added this formula just to show UDF might not be needed.