How to Convert Month name to Month Number in Excel

How to Convert Month name to Month Number in Excel

In this example, we will learn how to convert month`s name into its equivalent month number. In our example, Column A contains the 12 months name. We want to convert these months name into its equivalent month number. The process is given below.

  • Type the formula =MONTH(DATEVALUE(A2&”1″)) in cell B2
  • Drag the formulated cell B2 to B13
During our Advanced Microsoft Excel Training for Finance Team at Krones Nig Ltd, Ogba

It will show the months the number in column B. The following picture shows the result of this example.

Using the TIMEVALUE Function to Convert Text to Time in Excel

If in your spreadsheet you have values that have been inserted as time but are actually text values, then you have to use the TIMEVALUE function to convert these text values as time values. Let’s say in cell A2 we have the text string of time. We want to convert it to a valid time value in another cell. We will use the formula =TIMEVALUE(A2) for this. Using this value, we will find a numeric value which is not an actual time. We will need to format this numeric value into time value to show the exact time value.

The following picture shows how the process is done.

Our July 2019 Training Programmes are still running.

ADVANCED MICROSOFT EXCEL – LAGOS & ABUJA

DURATION: 3 DAYS

______________________________________________________________

JULY 2019 CLASSES – BASIC, INTERMEDIATE & ADVANCED EXCEL

DATE: 29TH – 31ST JULY 2019

WEEKDAY CLASSES:

DATE: 14TH – 16TH JULY 2019 (MON-WED) – BASIC TO INTERM

DATE: 17TH – 19TH JULY 2019 (THUR – SAT) – ADVANCED EXCEL

DATE: 21ST – 23RD JULY 2019 (MON-WED) – BASIC TO INTERM

DATE: 24TH – 26TH JULY 2019 (THUR – SAT) – ADVANCED EXCEL

TIME: 10 am – 3 pm

Click here to view details and Course Outline

Using Excel VBA to Convert Text to Number

To convert text to numbers in excel 1st you have to open the visual basic editor from the Developer tab. Then in the blank box apply this code and run it. You will see all your text formatted cells will convert to the general format.

There are so many codes available to do this. We used a shortcode to perform this task. The code that I used is given below which I collected from this link https://stackoverflow.com/questions/44826966/vba-how-to-convert-value-of-cell-from-text-to-number

Source code    

Sub macro()

Range(“A1:C10”).Select ‘specify the range which suits your purpose

With Selection

Selection.NumberFormat = “General”

.Value = .Value

End With

End Sub

Conclusion

As we can see there are so many methods available to convert text into a number. To perform an advanced arithmetic task, it is good a practice to convert the text formatted numbers into actual numbers if they require in any mathematical calculation.

I hope, by reading this blog you will find a good solution for converting Text to Time Value and Numbers

Abraham Eninla

Microsoft Office Certified Excel Specialist & Expert

Tel: 08134761579, Email: info@leadvisionconcepts.com.ng

Leave a Reply

Your email address will not be published. Required fields are marked *