Fix Names

[Portucale]

Hi,

I have a procedure which tends to fix logins from a "date" to "text" but it seems that it looses the numeric part. Please note that the information is extracted from a database.

Example: login is Jan10, when I bring this to excel it formats as Jan-10, with the procedure below I am able to change it to 'Jan01', is there any way that the 10 would be kept?

Public Sub FixIt()
  If TypeName(Selection) = "Range" Then
  With Selection
  .NumberFormat = "@"
  .Value = Evaluate("IF(ISNUMBER(" & .Address & "),TEXT(" & .Address & ",""mmmdd"")," & .Address & ")")
  End With
  End If
End Sub

Any help and all help is very much appreciated,

Kind regards,

 

[NARAYANK991]

Hi Portucale ,

Change the following line of code :

.Value = Evaluate("IF(ISNUMBER(" & .Address & "),TEXT(" & .Address & ",""mmmyy"")," & .Address & ")")

where the change is specifically highlighted.

Narayan

 

[Luke M]

Couple questions.
First, why are you changing your data from numbers into text? Normally, we want to go the other way as it gives us more flexibility.
Second, the date that came in is actual Jan 1, 2010. But, I see from your code, and your write-up, that you were expecting Jan 10 (of some year). Have I interpreted that correct in that you actually want to take the year from the date and turn it into the day? For more examples, if a date comes in and shows Jan-11 (which is Jan 1, 2011), what do you want to change this to?

 

[Portucale]

Hi Luke,

Apologies if I created confusion, right this is what happens:

A database has a column containing Login ID's like Jan10, Mar25, Jun15, etc. (Just a coincidence that IT creates logins like that...), so when we extract the necessary information, including the Login column excel interprets that these names are DATES, when in fact should be a NAME (text), so instead of excel display as Jan-10, Mar-25, Jun-15, etc. I need to reformat to Jan10, Mar25 and Jun15.

As you can see from the procedure above I can do this, in part, as the results given at the moment are: Jan01, Mar01 and Jun01..., just wondering what can I do within the process to give me the correct result, as there are quite a few logins which needs to be corrected manually

Thanks for the help,

 

[Portucale]

Thanks Narayank991 spot on, I feel bad as I didn't notice the date format...