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
Similar Threads
