Microsoft Office Forums Macro to read years into words

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-28-2019, 07:55 PM
Marcia's Avatar
Marcia Marcia is offline Macro to read years into words Windows 7 32bit Macro to read years into words Office 2007
Competent Performer
Macro to read years into words
 
Join Date: May 2018
Location: Philippines
Posts: 208
Marcia is on a distinguished road
Default Macro to read years into words


Hi, would somebody please provide the code that will automatically convert years in column A into words in column B, like:

cell A2 - 1914
In B2, the code should convert 1914 into "Nineteen Hundred Fourteen"
Cell A3 - 2019
In B2, "Twenty Hundred Nineteen"
This is used in issuing baptismal certificates that says,"in the year of our Lord Nineteen Hundred Fourteen"

Thank you.
Reply With Quote
  #2  
Old 01-28-2019, 08:12 PM
macropod's Avatar
macropod macropod is offline Macro to read years into words Windows 7 64bit Macro to read years into words Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,533
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Ordinarily, this kind of thing would be done with a mailmerge, using Word to extract the Excel data. In such a scenario, the text output is as simple as adding a CardText field switch to the relevant MERGEFIELD.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 01-28-2019, 08:33 PM
Marcia's Avatar
Marcia Marcia is offline Macro to read years into words Windows 7 32bit Macro to read years into words Office 2007
Competent Performer
Macro to read years into words
 
Join Date: May 2018
Location: Philippines
Posts: 208
Marcia is on a distinguished road
Default

Thank you for the quick reply macropod. Actually mail merge is being used in the preparation of the certificates with excel table as its source, but the excel data is in years 1900 to 2019. Typing the years in words is prone to errors so I am hoping that there's a code to automatically convert the numbers into words.
Reply With Quote
  #4  
Old 01-28-2019, 08:43 PM
macropod's Avatar
macropod macropod is offline Macro to read years into words Windows 7 64bit Macro to read years into words Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,533
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

As I said, the conversion to text:
Quote:
is as simple as adding a CardText field switch to the relevant MERGEFIELD
There is no need to do anything in Excel.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 01-28-2019, 08:53 PM
Marcia's Avatar
Marcia Marcia is offline Macro to read years into words Windows 7 32bit Macro to read years into words Office 2007
Competent Performer
Macro to read years into words
 
Join Date: May 2018
Location: Philippines
Posts: 208
Marcia is on a distinguished road
Default

Okay, I seldom work with Word so I need to search and orient myself what CARDTEXT is and what it does. It's a new term to me. I'll be back...
Reply With Quote
  #6  
Old 01-28-2019, 08:57 PM
macropod's Avatar
macropod macropod is offline Macro to read years into words Windows 7 64bit Macro to read years into words Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,533
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

See 'About field code syntax' in:
https://support.office.com/en-us/art...4-bab6ba6b06bb
and 'Number formats' in:
https://support.office.com/en-us/art...f-6c36ae43508c
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 01-28-2019, 11:47 PM
Marcia's Avatar
Marcia Marcia is offline Macro to read years into words Windows 7 32bit Macro to read years into words Office 2007
Competent Performer
Macro to read years into words
 
Join Date: May 2018
Location: Philippines
Posts: 208
Marcia is on a distinguished road
Default

Thank you again Paul for the link that you provided. After more than an hour of trial and error, I finally got the hang of applying the CardText switch. My problem is that the result shows "One Thousand Nine Hundred Thirty Nine" when it should be "Nineteen Hundred Thirty Nine". I will ask our Vicar if this is okay.
Reply With Quote
  #8  
Old 01-29-2019, 12:10 AM
macropod's Avatar
macropod macropod is offline Macro to read years into words Windows 7 64bit Macro to read years into words Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,533
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

There are ways of achieving the "Nineteen Hundred", but the field coding is convoluted to retain "Two Thousand". That said, archaic expressions such as "in the year of our Lord" would usually be accompanied by "One Thousand Nine Hundred" rather than "Nineteen Hundred".
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #9  
Old 01-29-2019, 11:50 PM
Marcia's Avatar
Marcia Marcia is offline Macro to read years into words Windows 7 32bit Macro to read years into words Office 2007
Competent Performer
Macro to read years into words
 
Join Date: May 2018
Location: Philippines
Posts: 208
Marcia is on a distinguished road
Default

It's a great discovery for me about the CARDTEXT and CARDORD in mail merging. The usual typo errors would be minimized by these switches. Thank you.
Reply With Quote
  #10  
Old 01-30-2019, 01:14 AM
macropod's Avatar
macropod macropod is offline Macro to read years into words Windows 7 64bit Macro to read years into words Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,533
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Marcia View Post
CARDORD
Presumably you meant OrdText.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #11  
Old 01-30-2019, 06:08 AM
Marcia's Avatar
Marcia Marcia is offline Macro to read years into words Windows 7 32bit Macro to read years into words Office 2007
Competent Performer
Macro to read years into words
 
Join Date: May 2018
Location: Philippines
Posts: 208
Marcia is on a distinguished road
Default

Right... Thanks for pointing the error..
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to read years into words Use macro on form activate attached to read only file smarti Word VBA 3 03-02-2017 09:06 AM
Macro to read years into words Macro to read cell contents and compare data jrfoley3 Excel Programming 7 07-09-2016 12:25 PM
Macro to read years into words Macro will not read the value of cell with a formula in it! grayson1231 Excel Programming 10 03-28-2015 03:47 PM
Email macro to read range for .To field MattMurdock Excel Programming 2 08-07-2012 06:46 AM
Macro to read years into words Macro to read word document harishankar.selvaraju Excel Programming 1 06-14-2012 03:48 AM


All times are GMT -7. The time now is 08:19 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft