Formula to Convert Month Name in Text to Month Number in Google Sheets

0
143
Convert Month Name in Text to Month Number

What is the function to convert month name in text to month number in Google Sheets? Actually there is no such function to do this. But you can do it by using a Date function in Google Doc Spreadsheets in a tricky way.

When I say converting month name in text to month number, I simply mean that when you type “December” the formula should return the month number 12.

Month in Text Google Sheets

Convert Month Name in Text to Month Number

I will explain you how to convert a month in Text format in Cell A3 to its corresponding month number.

Formula for this purpose is;

=MONTH(A3&1)

Explanation to the Use:

We can use Google Sheets month function to get the month number from date. But here we have NO DATE to return the month number. Instead we have month in text format. So first we should convert the text to any date format so that MONTH function can identify it. So we joined the Month in Text with a number like A3&1. So it automatically covert to a date like JANUARY 1 which Google Sheets MONTH function accept.

This way you can easily convert any month name in text to month number in Google Sheets. Now a practical use of it.

Here is a Practical Situation Where Converting Month Name in Text to Corresponding Month Number Comes Handy

How to Sort Month Names in Text Format to Month Wise Order

Suppose you have a list of months in text format. You know you can’t sort it to moth wise as it’s text.

Sort Month in Text in Proper Month Order

But we can do it the following way.

On the column next to the month in text, use the above same formula but as an array and sort the data.

Here is that formula.

=ArrayFormula(if(len(A1:A),(MONTH(A1:A&1)),””))

Now see how the sorting taking place.

month in text sort settings

The ArrayFormula is useful when the number of rows are very large. So whenever any correction is required to your formula you can do it at one place. No need to scroll up and down to edit the formula.

You can find similar scenarios to use this cool Google sheets date formula.

Keep visiting and enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here