Spelling dates in words

[Ganeszgk]

It's unknown Nobody for this Answer??? oh my god.....



Respected sir/Madam,
I have large number database of the Date of Birth 23/03/1985. The dob display another cell
[Twenty Three - March - Nineteen Fifty Eight].
For example:
A1: 28.8.1987
A2:[Twenty Eight-August-Nineteen Eighty Seven] this method want i.

How it work, please send coding will having and please to mention step by step process. Thank you..
i hoping...

 

[cchavez24]

highlight the column
right click
select format
find the time format you desire, or select 'custom'

Please let me know if you have any questions

 

[mjbeam]

=CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(".",A1)-1),"31","Thirty One"),"30","Thirty"),"29","Twenty Nine"),"28","Twenty Eight"),"27","Twenty Seven"),"26","Twenty Six"),"25","Twenty Five"),"24","Twenty Four"),"23","Twenty Three"),"22","Twenty Two"),"21","Twenty One"),"20","Twenty"),"19","Nineteen"),"18","Eighteen"),"17","Seventeen"),"16","Sixteen"),"15","Fifteen"),"14","Fourteen"),"13","Thirteen"),"12","Twelve"),"11","Eleven"),"10","Ten"),"9","Nine"),"8","Eight"),"7","Seven"),"6","Six"),"5","Five"),"4","Four"),"3","Three"),"2","Two"),"1","One")," - ",TEXT(INT(LEFT(MID(A1,FIND(".",A1)+1,3),FIND(".",MID(A1,FIND(".",A1)+1,3))-1)),"mmmm")," - ",SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+1,2),"20","Two Thousand"),"19","Nineteen Hundred")," ",IF(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,1)="1",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,2),"19","Nineteen"),"18","Eighteen"),"17","Seventeen"),"16","Sixteen"),"15","Fifteen"),"14","Fourteen"),"13","Thirteen"),"12","Twelve"),"11","Eleven"),"10","Ten"),IF(MID(MID(B1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,1)="0",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,2),"09","Nine"),"08","Eight"),"07","Seven"),"06","Six"),"05","Five"),"04","Four"),"03","Three"),"02","Two"),"01","One"),"00",""),CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,1),"9","Ninety "),"8","Eighty "),"7","Seventy "),"6","Sixty "),"5","Fifty "),"4","Fourty "),"3","Thirty "),"2","Twenty "),"0",""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+4,1),"0",""),"1","One"),"2","Two"),"3","Three"),"4","Four"),"5","Five"),"6","Six"),"7","Seven"),"8","Eight"),"9","Nine")))))

 

[Ganeszgk]

=CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(".",A1)-1),"31","Thirty One"),"30","Thirty"),"29","Twenty Nine"),"28","Twenty Eight"),"27","Twenty Seven"),"26","Twenty Six"),"25","Twenty Five"),"24","Twenty Four"),"23","Twenty Three"),"22","Twenty Two"),"21","Twenty One"),"20","Twenty"),"19","Nineteen"),"18","Eighteen"),"17","Seventeen"),"16","Sixteen"),"15","Fifteen"),"14","Fourteen"),"13","Thirteen"),"12","Twelve"),"11","Eleven"),"10","Ten"),"9","Nine"),"8","Eight"),"7","Seven"),"6","Six"),"5","Five"),"4","Four"),"3","Three"),"2","Two"),"1","One")," - ",TEXT(INT(LEFT(MID(A1,FIND(".",A1)+1,3),FIND(".",MID(A1,FIND(".",A1)+1,3))-1)),"mmmm")," - ",SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+1,2),"20","Two Thousand"),"19","Nineteen Hundred")," ",IF(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,1)="1",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,2),"19","Nineteen"),"18","Eighteen"),"17","Seventeen"),"16","Sixteen"),"15","Fifteen"),"14","Fourteen"),"13","Thirteen"),"12","Twelve"),"11","Eleven"),"10","Ten"),IF(MID(MID(B1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,1)="0",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,2),"09","Nine"),"08","Eight"),"07","Seven"),"06","Six"),"05","Five"),"04","Four"),"03","Three"),"02","Two"),"01","One"),"00",""),CONCATENATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+3,1),"9","Ninety "),"8","Eighty "),"7","Seventy "),"6","Sixty "),"5","Fifty "),"4","Fourty "),"3","Thirty "),"2","Twenty "),"0",""),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(MID(A1,FIND(".",A1)+1,9),FIND(".",MID(A1,FIND(".",A1)+1,9))+4,1),"0",""),"1","One"),"2","Two"),"3","Three"),"4","Four"),"5","Five"),"6","Six"),"7","Seven"),"8","Eight"),"9","Nine")))))

Sorry sir. i tried like this is format. but i cannot get this result..

 

[RoryA]

Please do not start duplicate threads, and do not ask people to take conversations off the forum and into email.

Thread closed, and email removed. Please post all answers to the original thread here: https://www.mrexcel.com/forum/excel-questions/993241-date-birth-word-conversion.html#post4769213