Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Dollar and date format
I have 2 questions related to formatting information that I have imported:
1)If I have a column that contains amounts such as 0000000427.99 and I want to remove the decimal but keep the trailing cents, what is the best way to do that for a whole column? example: 000000042799 2) I also have a date that has been imported as 3/9/2005 and I need to remove the "/"'s and have the month and date format with zeros if needed such as 03092005, what is the best way to accomplish this on a column. Thank you, Rick |
#2
|
|||
|
|||
For Question # 1 - Multiply by 100 :-)) Just kidding
For Question # 2 - Format Cells - Custom - mmddyyyy or mmddyyyy. "Rick" wrote: I have 2 questions related to formatting information that I have imported: 1)If I have a column that contains amounts such as 0000000427.99 and I want to remove the decimal but keep the trailing cents, what is the best way to do that for a whole column? example: 000000042799 2) I also have a date that has been imported as 3/9/2005 and I need to remove the "/"'s and have the month and date format with zeros if needed such as 03092005, what is the best way to accomplish this on a column. Thank you, Rick |
#3
|
|||
|
|||
I tried the multiply by 100, It works except I can't seem to figure out how
to apply a formula automatically to a column when the data is already there. Solution 2 worked perfectly! - Thanks "Ramakrishnan Rajamani" wrote: For Question # 1 - Multiply by 100 :-)) Just kidding For Question # 2 - Format Cells - Custom - mmddyyyy or mmddyyyy. "Rick" wrote: I have 2 questions related to formatting information that I have imported: 1)If I have a column that contains amounts such as 0000000427.99 and I want to remove the decimal but keep the trailing cents, what is the best way to do that for a whole column? example: 000000042799 2) I also have a date that has been imported as 3/9/2005 and I need to remove the "/"'s and have the month and date format with zeros if needed such as 03092005, what is the best way to accomplish this on a column. Thank you, Rick |
#4
|
|||
|
|||
What data do you have in the cells?
a. Hardcoded values b. Existing formulae "Rick" wrote: I tried the multiply by 100, It works except I can't seem to figure out how to apply a formula automatically to a column when the data is already there. Solution 2 worked perfectly! - Thanks "Ramakrishnan Rajamani" wrote: For Question # 1 - Multiply by 100 :-)) Just kidding For Question # 2 - Format Cells - Custom - mmddyyyy or mmddyyyy. "Rick" wrote: I have 2 questions related to formatting information that I have imported: 1)If I have a column that contains amounts such as 0000000427.99 and I want to remove the decimal but keep the trailing cents, what is the best way to do that for a whole column? example: 000000042799 2) I also have a date that has been imported as 3/9/2005 and I need to remove the "/"'s and have the month and date format with zeros if needed such as 03092005, what is the best way to accomplish this on a column. Thank you, Rick |
#5
|
|||
|
|||
When imported, they look like:
$31,504.68 $1,709.59 $22,338.40 $582.50 $951.50 I need to have 12 digits total and include the last 2 digits as cents without the decimal and dollar sign. Example $31,504.68 = 000003150468 I want to automate a column of these numbers using a macro. Rick "Ramakrishnan Rajamani" wrote: What data do you have in the cells? a. Hardcoded values b. Existing formulae "Rick" wrote: I tried the multiply by 100, It works except I can't seem to figure out how to apply a formula automatically to a column when the data is already there. Solution 2 worked perfectly! - Thanks "Ramakrishnan Rajamani" wrote: For Question # 1 - Multiply by 100 :-)) Just kidding For Question # 2 - Format Cells - Custom - mmddyyyy or mmddyyyy. "Rick" wrote: I have 2 questions related to formatting information that I have imported: 1)If I have a column that contains amounts such as 0000000427.99 and I want to remove the decimal but keep the trailing cents, what is the best way to do that for a whole column? example: 000000042799 2) I also have a date that has been imported as 3/9/2005 and I need to remove the "/"'s and have the month and date format with zeros if needed such as 03092005, what is the best way to accomplish this on a column. Thank you, Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) | |||
Moving numbers from one sheet to another by date | Excel Worksheet Functions |