Excel 2007 Conversion of number to word form
I want to convert date "dd/mm/yyyy" in to word form like 01/12/1990 to First December Nineteen ninety". I have to convert large data of my students for issuing T C. Can anybody help me?
microsoft-excel-2007
asked Nov 4 '12 at 15:56
MoideenkuttyMoideenkutty
612
add a comment |
I want to convert date "dd/mm/yyyy" in to word form like 01/12/1990 to First December Nineteen ninety". I have to convert large data of my students for issuing T C. Can anybody help me?
microsoft-excel-2007
asked Nov 4 '12 at 15:56
MoideenkuttyMoideenkutty
612
2
The "Nineteen Ninety" part is highly non-standard, so most likely you're writing a custom macro to achieve this. What have you tried already? Where are you getting stuck?
– Ƭᴇcʜιᴇ007
Nov 4 '12 at 17:28
See this Microsoft Support page for a general currency VBA solution to get you started
– chris neilsen
Nov 6 '12 at 8:04
add a comment |
I want to convert date "dd/mm/yyyy" in to word form like 01/12/1990 to First December Nineteen ninety". I have to convert large data of my students for issuing T C. Can anybody help me?
microsoft-excel-2007
asked Nov 4 '12 at 15:56
MoideenkuttyMoideenkutty
612
I want to convert date "dd/mm/yyyy" in to word form like 01/12/1990 to First December Nineteen ninety". I have to convert large data of my students for issuing T C. Can anybody help me?
microsoft-excel-2007
microsoft-excel-2007
asked Nov 4 '12 at 15:56
MoideenkuttyMoideenkutty
612
asked Nov 4 '12 at 15:56
MoideenkuttyMoideenkutty
612
asked Nov 4 '12 at 15:56
MoideenkuttyMoideenkutty
612
asked Nov 4 '12 at 15:56
MoideenkuttyMoideenkutty
612
asked Nov 4 '12 at 15:56
MoideenkuttyMoideenkutty
612
612
2
The "Nineteen Ninety" part is highly non-standard, so most likely you're writing a custom macro to achieve this. What have you tried already? Where are you getting stuck?
– Ƭᴇcʜιᴇ007
Nov 4 '12 at 17:28
See this Microsoft Support page for a general currency VBA solution to get you started
– chris neilsen
Nov 6 '12 at 8:04
add a comment |
2
The "Nineteen Ninety" part is highly non-standard, so most likely you're writing a custom macro to achieve this. What have you tried already? Where are you getting stuck?
– Ƭᴇcʜιᴇ007
Nov 4 '12 at 17:28
See this Microsoft Support page for a general currency VBA solution to get you started
– chris neilsen
Nov 6 '12 at 8:04
2
2
The "Nineteen Ninety" part is highly non-standard, so most likely you're writing a custom macro to achieve this. What have you tried already? Where are you getting stuck?
– Ƭᴇcʜιᴇ007
Nov 4 '12 at 17:28
The "Nineteen Ninety" part is highly non-standard, so most likely you're writing a custom macro to achieve this. What have you tried already? Where are you getting stuck?
– Ƭᴇcʜιᴇ007
Nov 4 '12 at 17:28
See this Microsoft Support page for a general currency VBA solution to get you started
– chris neilsen
Nov 6 '12 at 8:04
See this Microsoft Support page for a general currency VBA solution to get you started
– chris neilsen
Nov 6 '12 at 8:04
add a comment |
2 Answers
2
active
oldest
votes
There is no built-in Excel function to convert dates into text in the way you need it.
There are a number of possibilities how to do this.
The easiest way to do it is to use a VB macro, as techie007 suggested.
If you want to do it without macros, the following may help.
month: That's easy. You can display the month as text by using custom formatting (enter "MMMM" as custom formatting). As part of a formula you can do this with =TEXT(...), e.g.
=TEXT(A1,"MMMM")
will convert the date in A1 into a month name.
There is no formatting to present the day (except weekday, but that's not what you asked) or year as text. I can think of two functions you could use to look up the text:
You can use =CHOOSE(...) or =VLOOKUP(...).
Again, let's assume the date is in A1, you could get the day as a word by using
=CHOOSE(DAY(A1), "First", "Second", "Third", "Fourth", ...)
(add the remaining words instead of "...")
You can do something similar for the year, obviously limiting to the years that may occur.
For VLOOKUP you would make a list somewhere (e.g. on another sheet), first column contains the numbers (e.g. 1, 2, 3, ... for day), second columns the words (First, Second, Third, ...). Assume this list is on Sheet2 in cells A1:B31, the formula to lookup the day would be
=VLOOKUP(DAY(A1), Sheet2!A1:B31)
Work out day, month and year as explained above and concat them using & symbol.
I would go with the suggestion of techie007 though and use a VB macro to do this.
answered Nov 5 '12 at 13:19
ssollingerssollinger
359110
add a comment |
Largely covered by @ssollinger, but using a single formula and lookup table:
answered Mar 29 '13 at 21:51
pnutspnuts
5,53032239
add a comment |
protected by Community♦ Jan 30 '17 at 20:21
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
There is no built-in Excel function to convert dates into text in the way you need it.
There are a number of possibilities how to do this.
The easiest way to do it is to use a VB macro, as techie007 suggested.
If you want to do it without macros, the following may help.
month: That's easy. You can display the month as text by using custom formatting (enter "MMMM" as custom formatting). As part of a formula you can do this with =TEXT(...), e.g.
=TEXT(A1,"MMMM")
will convert the date in A1 into a month name.
There is no formatting to present the day (except weekday, but that's not what you asked) or year as text. I can think of two functions you could use to look up the text:
You can use =CHOOSE(...) or =VLOOKUP(...).
Again, let's assume the date is in A1, you could get the day as a word by using
=CHOOSE(DAY(A1), "First", "Second", "Third", "Fourth", ...)
(add the remaining words instead of "...")
You can do something similar for the year, obviously limiting to the years that may occur.
For VLOOKUP you would make a list somewhere (e.g. on another sheet), first column contains the numbers (e.g. 1, 2, 3, ... for day), second columns the words (First, Second, Third, ...). Assume this list is on Sheet2 in cells A1:B31, the formula to lookup the day would be
=VLOOKUP(DAY(A1), Sheet2!A1:B31)
Work out day, month and year as explained above and concat them using & symbol.
I would go with the suggestion of techie007 though and use a VB macro to do this.
answered Nov 5 '12 at 13:19
ssollingerssollinger
359110
add a comment |
There is no built-in Excel function to convert dates into text in the way you need it.
There are a number of possibilities how to do this.
The easiest way to do it is to use a VB macro, as techie007 suggested.
If you want to do it without macros, the following may help.
month: That's easy. You can display the month as text by using custom formatting (enter "MMMM" as custom formatting). As part of a formula you can do this with =TEXT(...), e.g.
=TEXT(A1,"MMMM")
will convert the date in A1 into a month name.
There is no formatting to present the day (except weekday, but that's not what you asked) or year as text. I can think of two functions you could use to look up the text:
You can use =CHOOSE(...) or =VLOOKUP(...).
Again, let's assume the date is in A1, you could get the day as a word by using
=CHOOSE(DAY(A1), "First", "Second", "Third", "Fourth", ...)
(add the remaining words instead of "...")
You can do something similar for the year, obviously limiting to the years that may occur.
For VLOOKUP you would make a list somewhere (e.g. on another sheet), first column contains the numbers (e.g. 1, 2, 3, ... for day), second columns the words (First, Second, Third, ...). Assume this list is on Sheet2 in cells A1:B31, the formula to lookup the day would be
=VLOOKUP(DAY(A1), Sheet2!A1:B31)
Work out day, month and year as explained above and concat them using & symbol.
I would go with the suggestion of techie007 though and use a VB macro to do this.
answered Nov 5 '12 at 13:19
ssollingerssollinger
359110
add a comment |
There is no built-in Excel function to convert dates into text in the way you need it.
There are a number of possibilities how to do this.
The easiest way to do it is to use a VB macro, as techie007 suggested.
If you want to do it without macros, the following may help.
month: That's easy. You can display the month as text by using custom formatting (enter "MMMM" as custom formatting). As part of a formula you can do this with =TEXT(...), e.g.
=TEXT(A1,"MMMM")
will convert the date in A1 into a month name.
There is no formatting to present the day (except weekday, but that's not what you asked) or year as text. I can think of two functions you could use to look up the text:
You can use =CHOOSE(...) or =VLOOKUP(...).
Again, let's assume the date is in A1, you could get the day as a word by using
=CHOOSE(DAY(A1), "First", "Second", "Third", "Fourth", ...)
(add the remaining words instead of "...")
You can do something similar for the year, obviously limiting to the years that may occur.
For VLOOKUP you would make a list somewhere (e.g. on another sheet), first column contains the numbers (e.g. 1, 2, 3, ... for day), second columns the words (First, Second, Third, ...). Assume this list is on Sheet2 in cells A1:B31, the formula to lookup the day would be
=VLOOKUP(DAY(A1), Sheet2!A1:B31)
Work out day, month and year as explained above and concat them using & symbol.
I would go with the suggestion of techie007 though and use a VB macro to do this.
answered Nov 5 '12 at 13:19
ssollingerssollinger
359110
There is no built-in Excel function to convert dates into text in the way you need it.
There are a number of possibilities how to do this.
The easiest way to do it is to use a VB macro, as techie007 suggested.
If you want to do it without macros, the following may help.
month: That's easy. You can display the month as text by using custom formatting (enter "MMMM" as custom formatting). As part of a formula you can do this with =TEXT(...), e.g.
=TEXT(A1,"MMMM")
will convert the date in A1 into a month name.
There is no formatting to present the day (except weekday, but that's not what you asked) or year as text. I can think of two functions you could use to look up the text:
You can use =CHOOSE(...) or =VLOOKUP(...).
Again, let's assume the date is in A1, you could get the day as a word by using
=CHOOSE(DAY(A1), "First", "Second", "Third", "Fourth", ...)
(add the remaining words instead of "...")
You can do something similar for the year, obviously limiting to the years that may occur.
For VLOOKUP you would make a list somewhere (e.g. on another sheet), first column contains the numbers (e.g. 1, 2, 3, ... for day), second columns the words (First, Second, Third, ...). Assume this list is on Sheet2 in cells A1:B31, the formula to lookup the day would be
=VLOOKUP(DAY(A1), Sheet2!A1:B31)
Work out day, month and year as explained above and concat them using & symbol.
I would go with the suggestion of techie007 though and use a VB macro to do this.
answered Nov 5 '12 at 13:19
ssollingerssollinger
359110
answered Nov 5 '12 at 13:19
ssollingerssollinger
359110
answered Nov 5 '12 at 13:19
ssollingerssollinger
359110
answered Nov 5 '12 at 13:19
ssollingerssollinger
359110
359110
add a comment |
add a comment |
Largely covered by @ssollinger, but using a single formula and lookup table:
answered Mar 29 '13 at 21:51
pnutspnuts
5,53032239
add a comment |
Largely covered by @ssollinger, but using a single formula and lookup table:
answered Mar 29 '13 at 21:51
pnutspnuts
5,53032239
add a comment |
Largely covered by @ssollinger, but using a single formula and lookup table:
answered Mar 29 '13 at 21:51
pnutspnuts
5,53032239
Largely covered by @ssollinger, but using a single formula and lookup table:
answered Mar 29 '13 at 21:51
pnutspnuts
5,53032239
answered Mar 29 '13 at 21:51
pnutspnuts
5,53032239
answered Mar 29 '13 at 21:51
pnutspnuts
5,53032239
answered Mar 29 '13 at 21:51
pnutspnuts
5,53032239
5,53032239
add a comment |
add a comment |
protected by Community♦ Jan 30 '17 at 20:21
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?
2
The "Nineteen Ninety" part is highly non-standard, so most likely you're writing a custom macro to achieve this. What have you tried already? Where are you getting stuck?
– Ƭᴇcʜιᴇ007
Nov 4 '12 at 17:28
See this Microsoft Support page for a general currency VBA solution to get you started
– chris neilsen
Nov 6 '12 at 8:04