# Convert Number to Text in Excel



## etopro

How on earth do I convert a number in one cell to text in another? I click the formula link, choose TEXT and TEXT, enter the target cell, but then I'm stymied with the Format_text thingamajig. What do I put there? It looks as if I should click Format|Cell, but the Cell option is grayed out. What do I do?


----------



## Glaswegian

Hi

Do you want "5" as a numeral in one cell to appear as "five", alphabetical, in another cell? If that's what you want, then the easiest way is to use ASAP Utilities Add-In - it has a built in function to do that.

If I've misunderstood, could you explain in a bit more detail.


----------



## etopro

Sorry, but I did not give enough detail. I wish to display a formula in a cell based on the values of several other cells. For example, I wish cell C3 to display a text formula that is based on the contents of cells C1 and C2. If the formula is D=Q*[C1]+[C2], and cell C1 contains 2.3, and cell A2 contains 4.5, cell C3 should display D=Q*2.3+4.5. Can this be done? Thanks in advance for your help.


----------



## Glaswegian

I understand but I don't believe that can be done. No matter what function you use, Text or Value or whatever, unless you place an equals sign before the D, all you will get is the formula - not the values. And as soon as you place an equals sign before the D, Excel will try to calculate a value and, of course, will return an error, because it cannot evaluate D or Q.

Is there a particular reason for trying this? What are you trying to achieve? There may be another way to work this.


----------



## etopro

I may have thrown in a red herring with the equals sign. The nut of the problem is this: If cell C1 has the numerical value 2.3, can Excel put "2.3" as text in another cell? Sorry for the confusion, and thanks again for your help.


----------



## Glaswegian

lol - let's see if I understand. You have a numerical value of 2.3 in a cell and you want this converted to a "Text" value in another cell - by "Text" I *don't* mean "two point three". What you want is 2.3 shown as a text value rather than a numerical value - is that correct?


----------



## etopro

Yes, that is correct.


----------



## Glaswegian

It would just be

*=TEXT(C1,"0.00")*

where the format is an option on the Number Tab. The Text function converts a number to formatted text, and the result is no longer calculated as a number. 

Is this what you mean?


----------



## etopro

Thank you. It's hard to see how so simple a thing is covered so badly by Excel's Help utility. At least Excel itself remains a thing of beauty. You have been a true help.


----------



## DigitalAddict

Glaswegian,

Similiar to his question, I have a very simple roadblock myself.

Cell:
A1 has "2/5/07" in Date Format
B2 need to have the same but converted to TEXT.

When I simply enter B2's formula of "=A1" it comes back with the date serial number in B2's box, even though B2's formatting is set to TEXT.

How do I put A1's date contents into B2's as a text string. I can't believe I'm stuck on something so simple and can't find a straight answer. Thanks so much!


----------



## Glaswegian

Hi and welcome.

It's just the same idea as I provided to etopro.

Cell B2 should be formatted as "General". Then in B2 the formula would be

*=TEXT(A1,"dd/mm/yy")*

although I guess your format would be "mm/dd/yy".


----------



## mohan7805

how can I convert 11/02/2008 to eleventh february Two thousand two


----------



## mohan7805

It's mohan again. I posted one thread and waited for a reply. upto yesterday I found that about 6000 read and only 11 responded, which made me to in from of my system to find a solution. I found it ( I have only small knowledge in programming. and I am an under graduate and working as a school teacher in primary standard). The programme I made work smoothly from 1/1/1962 to 31/12/2050(that I tested and found correct may be lengthy. Pls help me to make it short and easy to use. Of course I made it with the help of internet. I do not know what exactly the words stand for. I do enclose the programme. 
Request viewer's sugections and hep. Thank You.
Private Function Spelldate(ByVal datDate As Date)
Dim strDay As String
Dim strMonth As String
Dim strYear As String
Dim lngDay As Long

strYear = Format(datDate, "YYYY")
strMonth = Format(datDate, "MMMM")
strDay = Format(datDate, "D")
Select Case strDay
Case 1
strDay = "First"
Case 2
strDay = "Second"
Case 3
strDay = "Third"
Case 4
strDay = "Fourth"
Case 5
strDay = "Fifth"
Case 6
strDay = "Sixth"
Case 7
strDay = "Seventh"
Case 8
strDay = "Eighth"
Case 9
strDay = "Ninth"
Case 10
strDay = "Tenth"
Case 11
strDay = "Eleventh"
Case 12
strDay = "Twelfth"
Case 13
strDay = "Thirteenth"
Case 14
strDay = "Fourteenth"
Case 15
strDay = "Fifteenth"
Case 16
strDay = "Sixteenth"
Case 17
strDay = "Seventeenth"
Case 18
strDay = "Eighteenth"
Case 19
strDay = "Ninteenth"
Case 20
strDay = "Twentieth"
Case 21
strDay = "Twenty-first"
Case 22
strDay = "Twenty-second"
Case 23
strDay = "Twenty-third"
Case 24
strDay = "Twenty-fourth"
Case 25
strDay = "Twenty-fifth"
Case 26
strDay = "Twenty-sixth"
Case 27
strDay = "Twenty-seventh"
Case 28
strDay = "Twenty-eighth"
Case 29
strDay = "Twenty-ninth"
Case 30
strDay = "Thirtieth"
Case 31
strDay = "Thirty-first"
End Select
Dim lngyear As Long
strYear = Format(datDate, "YYYY")
Dim a, b, c As String
c = Year(datDate)
a = Left(c, 2)
b = Right(c, 2)
Select Case a
Case 1
a = "One"
Case 2
a = "Two"
Case 3
a = "Three"
Case 4
a = "Four"
Case 5
a = "Five"
Case 6
a = "Six"
Case 7
a = "Seven"
Case 8
a = "Eight"
Case 9
a = "Nine"
Case 10
a = "Ten"
Case 11
a = "Eleven"
Case 12
a = "Twelve"
Case 13
a = "Thirteen"
Case 14
a = "Fourteen"
Case 15
a = "Fifteen"
Case 16
a = "Sixteen"
Case 17
a = "Seventeen"
Case 18
a = "Eighteen"
Case 19
a = "Nineteen"
Case 20
a = "Two Thousand"
End Select
If b = 1 Then
b = "One"
ElseIf b = 2 Then
b = "Two"
ElseIf b = 3 Then
b = "Three"
ElseIf b = 4 Then
b = "Four"
ElseIf b = 5 Then
b = "Five"
ElseIf b = 6 Then
b = "Six"
ElseIf b = 7 Then
b = "Seven"
ElseIf b = 8 Then
b = "Eight"
ElseIf b = 9 Then
b = "Nine"
ElseIf b = 10 Then
b = "Ten"
ElseIf b = 11 Then
b = "Eleven"
ElseIf b = 12 Then
b = "Twelve"
ElseIf b = 13 Then
b = "Thirteen"
ElseIf b = 14 Then
b = "Fourteen"
ElseIf b = 15 Then
b = "Fifteen"
ElseIf b = 16 Then
b = "Sixteen"
ElseIf b = 17 Then
b = "Seventeen"
ElseIf b = 18 Then
b = "Eighteen"
ElseIf b = 19 Then
b = "Nineteen "
ElseIf b = 20 Then
b = "Twenty "
ElseIf b = 21 Then
b = "Twenty One "
ElseIf b = 22 Then
b = "Twenty Two "
ElseIf b = 23 Then
b = "Twenty Three "
ElseIf b = 24 Then
b = "Twenty Four "
ElseIf b = 25 Then
b = "Twenty Five "
ElseIf b = 26 Then
b = "Twenty Six "
ElseIf b = 27 Then
b = "Twenty Seven "
ElseIf b = 28 Then
b = "Twenty Eight "
ElseIf b = 29 Then
b = "Twenty Nine "
ElseIf b = 30 Then
b = "Thirty "
ElseIf b = 31 Then
b = "Thirty One "
ElseIf b = 32 Then
b = "Thirty Two "
ElseIf b = 33 Then
b = "Thirty Three "
ElseIf b = 34 Then
b = "Thirty Four "
ElseIf b = 35 Then
b = "Thirty Five "
ElseIf b = 36 Then
b = "Thirty Six "
ElseIf b = 37 Then
b = "Thirty Seven "
ElseIf b = 38 Then
b = "Thirty Eight "
ElseIf b = 39 Then
b = "Thirty Nine "
ElseIf b = 40 Then
b = "Fourty "
ElseIf b = 41 Then
b = "Fourty One "
ElseIf b = 42 Then
b = "Fourty Two "
ElseIf b = 43 Then
b = "Fourty Three "
ElseIf b = 44 Then
b = "Fourty Four "
ElseIf b = 45 Then
b = "Fourty Five "
ElseIf b = 46 Then
b = "Fourty Six "
ElseIf b = 47 Then
b = "Fourty Seven "
ElseIf b = 48 Then
b = "Fourty Eight "
ElseIf b = 49 Then
b = "Fourty Nine "
ElseIf b = 50 Then
b = "Fifty "
ElseIf b = 51 Then
b = "Fifty One "
ElseIf b = 52 Then
b = "Fifty Two "
ElseIf b = 53 Then
b = "Fifty Three "
ElseIf b = 54 Then
b = "Fifty Four "
ElseIf b = 55 Then
b = "Fifty Five "
ElseIf b = 56 Then
b = "Fifty Six "
ElseIf b = 57 Then
b = "Fifty Seven "
ElseIf b = 58 Then
b = "Fifty Eight "
ElseIf b = 59 Then
b = "Fifty Nine "
ElseIf b = 60 Then
b = "Sixty "
ElseIf b = 61 Then
b = "Sixty One "
ElseIf b = 62 Then
b = "Sixty Two "
ElseIf b = 63 Then
b = "Sixty Three "
ElseIf b = 64 Then
b = "Sixty Four "
ElseIf b = 65 Then
b = "Sixty Five "
ElseIf b = 66 Then
b = "Sixty Six "
ElseIf b = 67 Then
b = "Sixty Seven "
ElseIf b = 68 Then
b = "Sixty Eight "
ElseIf b = 69 Then
b = "Sixty Nine "
ElseIf b = 70 Then
b = "Seventy "
ElseIf b = 71 Then
b = "Seventy One "
ElseIf b = 72 Then
b = "Seventy Two "
ElseIf b = 73 Then
b = "Seventy Three "
ElseIf b = 74 Then
b = "Seventy Four "
ElseIf b = 75 Then
b = "Seventy Five "
ElseIf b = 76 Then
b = "Seventy Six "
ElseIf b = 77 Then
b = "Seventy Seven "
ElseIf b = 78 Then
b = "Seventy Eight "
ElseIf b = 79 Then
b = "Seventy Nine "
ElseIf b = 80 Then
b = "Eighty "
ElseIf b = 81 Then
b = "Eighty One "
ElseIf b = 82 Then
b = "Eighty Two "
ElseIf b = 83 Then
b = "Eighty Three "
ElseIf b = 84 Then
b = "Eighty Four "
ElseIf b = 85 Then
b = "Eighty Five "
ElseIf b = 86 Then
b = "Eighty Six "
ElseIf b = 87 Then
b = "Eighty Seven "
ElseIf b = 88 Then
b = "Eighty Eight "
ElseIf b = 89 Then
b = "Eighty Nine "
ElseIf b = 90 Then
b = "Ninety "
ElseIf b = 91 Then
b = "Ninety One "
ElseIf b = 92 Then
b = "Ninety Two "
ElseIf b = 93 Then
b = "Ninety Three "
ElseIf b = 94 Then
b = "Ninety Four "
ElseIf b = 95 Then
b = "Ninety Five "
ElseIf b = 96 Then
b = "Ninety Six "
ElseIf b = 97 Then
b = "Ninety Seven "
ElseIf b = 98 Then
b = "Ninety Eight "
ElseIf b = 99 Then
b = "Ninety Nine "
ElseIf b = 0 Then
b = " "
End If

Spelldate = strDay & " " & strMonth & " " & a & " " & b

End Function


----------



## Glaswegian

I'm sure ASAP Utilities (see Post #2 for link) does this...


----------



## Thumor

Here is something more interesting problem I have. My Column A has numbers. But I need them as string or text. So I changed column type to TEXT. All my numbers show as text as got left-aligned. I need strings with 0 padded numbers. From another forum, I used Custom type "000" or "0000" or "00000" as needed. But as soon as I change to Custom, the cell number turns back to 'numeric' - but still shows the zero pad. 
Ex. A1=1, A2=21, A3=321, A4=4321 and A5=54321, changing cell A1 type to Custom 000, A1 shows 001, but it changed to numeric type and I can use it in numeric operations. I need to change it to TEXT type.
A2 would be '0021', A3 remains Text type (as set on Entire column A), A4 needs to become '04321' if changed to Custom 00000.
I hope I am clear. Let me know if I can explain further.
Basically, the custom format shows me what I need, only in Text so when I export to csv, it remains 0 padded.


----------



## RSpecianjr

Hey Thumor,

I am sure there is an easier way to do this, but I usually use a formula like, =text(A2,"000") to get the value, then I copy and paste values. Lastly I change the format from general to text. This should give you 021 in text format, which you can then paste over the original data.

Hope this helps,

Robert D. Specian Jr.


----------

