• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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.
0
RadhaKrishnaKiJaya
Asked:
RadhaKrishnaKiJaya
  • 4
  • 3
  • 2
1 Solution
 
lwadwellCommented:
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.
0
 
nutschCommented:
format your cells as ddmmmyy (Ctrl+1 \ Number \ Custom) and you will get your 23Jan10 back

Posting your macro might help.

Thomas
0
 
RadhaKrishnaKiJayaAuthor Commented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lwadwellCommented:
>>   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.
0
 
nutschCommented:
Select the cell,
Type Ctrl+1 to get to the cell format dialog box
Number \ Custom \ ddmmmyy

Thomas
0
 
RadhaKrishnaKiJayaAuthor Commented:
nutsch, I already checked that and there is not ddmmmyy format available.  Anyway to add that to the list?  Thank you for your help.
0
 
RadhaKrishnaKiJayaAuthor Commented:
Hi nutsch,
I tried the steps and created a new format and tried to use it.  But, as soon as the macro is executed, dates are replaced with numbers.

But, I found a way around.  Use the cell as a Text field and enter dates in ddMMMyy format, i.e. 25SEP12, 01AUG12 etc.  But when the date is entered in slightest different way as 25Sep12, it is replaced by numbers.

Thank you very much for your help.
0
 
RadhaKrishnaKiJayaAuthor Commented:
Already described how to resolve this issue.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now