Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-28-2019, 07:55 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 154
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 Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,000
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 Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 154
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 Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,000
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 Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 154
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 Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,000
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 Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 154
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 Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,000
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 Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 154
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 Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,000
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 Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 154
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
Use macro on form activate attached to read only file smarti Word VBA 3 03-02-2017 09:06 AM
Macro to read cell contents and compare data jrfoley3 Excel Programming 7 07-09-2016 12:25 PM
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 word document harishankar.selvaraju Excel Programming 1 06-14-2012 03:48 AM


All times are GMT -7. The time now is 06:18 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft