Skip to main content

Converting Numbers or Date stored in Text format to Number or Date format

















Hi Everyone,
Sometimes we export data from our Softwares to Excel files and find that some data is looking as Numbers but not behaving as Numbers and same is the case with Dates as well. Here in this article, I have tried to overcome this problem with a procedure OR code which will convert the Numbers and Dates stored in Text format to their respective formats.

Here are the steps:-
1. Select the Range. The Range should be one columnar Range. Preferably select whole column at a time.
2. Click "Text to Column" Option in Data Tab.
3. Select "Delimited" Option, and click Next. Uncheck all the Dilimiter options.
4. Click Next Again and then click Finished. That it.
Now, you will notice that the format has changed.



Sometimes, Our Data include many columns. In this case, it won't be efficient to go for each column one by one. So I have written a VBA code that will take care of all the columns in the Active Sheet.

Here is the code:-

       
Sub TextToColumns()
    For c = 1 To ActiveSheet.UsedRange.Columns.Count
        'For Avoiding error arising because of empty columns.
        If Columns(c).Cells(1, 1) = "" Then Columns(c).Cells(1, 1) = "|"
        'Changing Numbers stored as Text to Numbers using Text to columns technique
        Columns(c).TextToColumns Destination:=Columns(c).Cells(1, 1), _
                                 DataType:=xlFixedWidth, _
                                 FieldInfo:=Array(0, 1), _
                                 TrailingMinusNumbers:=True
        If Columns(c).Cells(1, 1) = "|" Then Columns(c).Cells(1, 1).Clear
    Next
End Sub

Paste the above code by inserting a new module in the VBA Window. And Run it by using F5 or you can opt for inserting a Button for Running the TextToColumns Macro.

Regards,
Vikas Gautam

Labels:

Comments

Post a Comment

Popular posts from this blog

Automation:- Sending Invitation to Meeting Using Excel VBA

Hello Everyone, In one of the previous post, I wrote about automating Sending Emails using Excel VBA. This time I have come up with a pretty similar code. The code below sends Outlook Meeting Invitations to recipients on one click. here is the Code:- Sub Send_Invite_Auto() Dim olApp As Outlook.Application Dim olApt As AppointmentItem Set olApp = New Outlook.Application 'Creating Outlook Session Set olApt = olApp.CreateItem(olAppointmentItem) 'Creating an Appointment With olApt .Subject = "Enter the subject here." 'Subject .Start = DateAdd("d", 5, Now) 'Enter Date + Time here. .Recipients.Add ("example@gmail.com") 'Recipient Name, Alias, or any other Attribute. .MeetingStatus = olMeeting 'olAppointmentItem with Meeting status olMeeting 'becom...
10 comments
Read more

Making Password Protected PDFs using Excel Vba and PDFtk Tool

Hi Everyone, This time, I have come up a VBA Code to generate Password protected PDFs using Excel. Actually, Excel Vba has .ExportAsFixedFormat Method to generate PDFs but this hasn't any Parameter which takes password to protect the PDFs. So I have used PDFtk Tool which provide Command Line Interface to make PDFs protected using Password. Actually, You can do various things using PDFtk Tool command line varying from creating, merging, Protecting and many other. So Download the PDFtk Tool from the following link:- Download PDFtk Tookit Here are the Steps:- 1. Install the PDFtk Toolkit. 2. Use the following code to Print or Export the Activesheet with a password.
8 comments
Read more

Highlighting Duplicates across multiple sheets

Hi One and All, This time I have come up with some conditional formatting stuff. The aim is to highlight the duplicates across multiple sheets and with in the sheet as well. Assumptions:- 1. I am assuming that sheet names goes on like sheet1, sheet2, sheet3.... 2. The Target Column No. is same in all the sheets. I mean, as in the attached example, its Column A which is being targeted in both sheets.
2 comments
Read more