Convert Numbers to Text

A

Arjun

Joined
Jul 8, 2009
Messages
6
Reaction score
0
Hi,
Is it possible to convert a number entered in a cell to its text format in Excel?
For example, 263 is converted to Two Hundred Sixty Three. And How?
Thanks in advance
Beas
 
There are VBA solutions out there but try this formula solution
(for whole numbers up to 999,999,999):
Cell references are in bold – copy and paste into each cell the following

A2 Number

C1 =D1+1 D1 =E1+1 E1 =F1+1 F1 =G1+1

G1 =H1+1 H1 =I1+1 I1 =J1+1 J1 =K1+1

K1 0



Q6 0 R6 Zero S6 Ten

Q7 1 R7 One S7 Eleven T7 Ten

Q8 2 R8 Two S8 Twelve T8 Twenty

Q9 3 R9 Three S9 Thirteen T9 Thirty

Q10 4 R10 Four S10 Fourteen T10 Forty

Q11 5 R11 Five S11 Fifteen T11 Fifty

Q12 6 R12 Six S12 Sixteen T12 Sixty

Q13 7 R13 Seven S13 Seventeen T13 Seventy

Q14 8 R14 Eight S14 Eighteen T14 Eighty

Q15 9 R15 Nine S15 Nineteen T15 Ninety

Q16 10 T16 Hundred



C2 Hundred thousand thousand

C3 =IF(ISERROR(1*MID($A3,$L3-C1,1)),"",1*MID($A3,$L3-C1,1))

D2 Ten thousand thousand

D3 =IF(ISERROR(1*MID($A3,$L3-D1,1)),"",1*MID($A3,$L3-D1,1))

E2 Thousand thousand

E3 =IF(ISERROR(1*MID($A3,$L3-E1,1)),"",1*MID($A3,$L3-E1,1))

F2 Hundred thousands

F3 =IF(ISERROR(1*MID($A3,$L3-F1,1)),"",1*MID($A3,$L3-F1,1))

G2 Ten thousands

G3 =IF(ISERROR(1*MID($A3,$L3-G1,1)),"",1*MID($A3,$L3-G1,1))

H2 Thousands

H3 =IF(ISERROR(1*MID($A3,$L3-H1,1)),"",1*MID($A3,$L3-H1,1))

I2 Hundreds

I3 =IF(ISERROR(1*MID($A3,$L3-I1,1)),"",1*MID($A3,$L3-I1,1))

J2 Tens

J3 =IF(ISERROR(1*MID($A3,$L3-J1,1)),"",1*MID($A3,$L3-J1,1))

K2 Units

K3 =IF(ISERROR(1*MID($A3,$L3-K1,1)),"",1*MID($A3,$L3-K1,1))

L2 No. of digits

L3 {=COUNT(1*MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))} [Entered

N2 Hundred Million

N3 =IF(ISERROR(IF(C3=0,"",VLOOKUP(C3,Q6:R16,2)&"hundred "&IF(AND(D3=0,E3=0),"million ","and "))),"",IF(C3=0,"",VLOOKUP(C3,Q6:R16,2)&"hundred "&IF(AND(D3=0,E3=0),"million ","and ")))

O2 Million

O3 =IF(ISERROR(IF(D3=0,"",IF(AND(D3=1,E3<>0),VLOOKUP(E3,Q6:S16,3)&"million ",VLOOKUP(D3,Q6:T16,4)&IF(E3=0,"million ","")))),"",IF(D3=0,"",IF(AND(D3=1,E3<>0),VLOOKUP(E3,Q6:S16,3)&"million ",VLOOKUP(D3,Q6:T16,4)&IF(E3=0,"million ",""))))

P2 Million

P3 =IF(ISERROR(IF(OR(E3=0,AND(D3=1,E3>=1,E3<=9)),"",VLOOKUP(E3,Q6:R16,2)&"million ")),"",IF(OR(E3=0,AND(D3=1,E3>=1,E3<=9)),"",VLOOKUP(E3,Q6:R16,2)&"million "))

Q2 Hundred Thousands

Q3 =IF(ISERROR(IF(F3=0,"",VLOOKUP(F3,Q6:R16,2)&IF(SUM(G3:H3)=0,"hundred thousand ","hundred and "))),"",IF(F3=0,"",VLOOKUP(F3,Q6:R16,2)&IF(SUM(G3:H3)=0,"hundred thousand ","hundred and ")))

R2 thousand

R3 =IF(ISERROR(IF(G28=0,"",IF(AND(G28=1,H28<>0),VLOOKUP(H28,Q31:S41,3)&

IF(OR(MOD(A28,10000)=0,AND(G28=1,OR(H28>=1,H28<=9))),"thousand ",""),VLOOKUP(G28,Q31:T41,4)&

IF(H28=0,"thousand ","")))),"",IF(G28=0,"",IF(AND(G28=1,H28<>0),VLOOKUP(H28,Q31:S41,3)&

IF(OR(MOD(A28,10000)=0,AND(G28=1,OR(H28>=1,H28<=9))),"thousand ",""),VLOOKUP(G28,Q31:T41,4)&

IF(H28=0,"thousand ",""))))

S2 thousand

S3 =IF(ISERROR(IF(OR(H3=0,AND(G3=1,OR(H3>=1,H3<=9))),"",VLOOKUP(H3,Q6:R16,2)&"thousand ")),"",IF(OR(H3=0,AND(G3=1,OR(H3>=1,H3<=9))),"",VLOOKUP(H3,Q6:R16,2)&"thousand "))

T2 hundred

T3 =IF(ISERROR(IF(I3=0,"",VLOOKUP(I3,Q6:R16,2)&"hundred ")),"",IF(I3=0,"",VLOOKUP(I3,Q6:R16,2)&"hundred "))

U2 Tens

U3 =IF(OR(A3<10,J3=0),"",IF(MOD(A3,100)=0,"",IF(OR(A3<20,AND(1*RIGHT(A3,2)>=11,1*RIGHT(A3,2)<=19)),

IF(L3>2,"and ","")&VLOOKUP(K3,Q6:S16,3),IF(L3>2,"and ","")&VLOOKUP(J3,Q6:T16,4))))

V2 Units

V3 =IF(A3=0,R6,IF(OR(K3=0,AND(1*RIGHT(A3,2)>=11,1*RIGHT(A3,2)<=19)),"",IF(A3<=10,IF(AND(J3=0,L3>2),

"and ","")&VLOOKUP(K3,Q6:R16,2),IF(AND(A3<=99,MOD(A3,10)=0),VLOOKUP(K3,Q6:T16,4),IF(A3>20,

IF(AND(J3=0,L3>2),"and ","")&VLOOKUP(K3,Q6:R16,2),"")))))

X2 Result

X3 =N3&O3&P3&Q3&R3&S3&T3&U3&V3

Hide columns C to V

Enter number in A3 and will appear in words in X3

Hope this gives you what you want
 
Thanks that ought to do it. I was looking for one of the excel supplied functions. Thanks anyway , great effort.
Arjun
 
Back
Top