RadhaKrishnaKiJaya
asked on
Text field changes to Numeric
Hi Experts,
I am working on Excel sheet (Excel 2007), changing a Numeric field to Text. FYI, a VBA macro attached to the sheet. When I run the macro, immediately the Text field changes to Numeric and the value in it becomes number. For example, 23Jan10 changes to 40201.00. I keep changing the field to a Text, but changes to Numeric soon after I run the macro. Please let me know if I am not clear on my question. Thank you in advance.
I am working on Excel sheet (Excel 2007), changing a Numeric field to Text. FYI, a VBA macro attached to the sheet. When I run the macro, immediately the Text field changes to Numeric and the value in it becomes number. For example, 23Jan10 changes to 40201.00. I keep changing the field to a Text, but changes to Numeric soon after I run the macro. Please let me know if I am not clear on my question. Thank you in advance.
Lee Wadwell
Not exactly sure what your question is ... but dates are numeric. The text you might see in a date field (e.g Jan) is purely done via a format - the underlying value is a number.
format your cells as ddmmmyy (Ctrl+1 \ Number \ Custom) and you will get your 23Jan10 back
Posting your macro might help.
Thomas
Posting your macro might help.
Thomas
ASKER
lwadwell & nutsch, Thank you for your response. I just want to leave the way it is entered. Not change it to a number. FYI, the cell is a Text field and automatically changes to Numeric field. I am trying to change the default format to ddmmmyy, but the format is missing from the list (right click on the cell --> choose Date --> you would see the list of different format on your right). Please let me know if you have any other idea.
>> I just want to leave the way it is entered. Not change it to a number.
It was already a number ... the value was not changed, only the formatting changed.
I sounds like the macro might be doing something that removes the date formatting ... without knowledge of the macro itself - impossible to tell.
It was already a number ... the value was not changed, only the formatting changed.
I sounds like the macro might be doing something that removes the date formatting ... without knowledge of the macro itself - impossible to tell.
Select the cell,
Type Ctrl+1 to get to the cell format dialog box
Number \ Custom \ ddmmmyy
Thomas
Type Ctrl+1 to get to the cell format dialog box
Number \ Custom \ ddmmmyy
Thomas
ASKER
nutsch, I already checked that and there is not ddmmmyy format available. Anyway to add that to the list? Thank you for your help.
yes, try http://office.microsoft.com/en-gb/excel-help/create-or-delete-a-custom-number-format-HP005199500.aspx for instructions
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Already described how to resolve this issue.