Excel Text Formula

Irina Iatco · 09/14/20

Hi,

I am trying to use an Excel formula to extract the full month from the date. I am using the excel formula: text([Due Date]@row,"mmmm") and I am getting an error. Is there a similar smartsheet formula ?

Thank you !

MCorbin · 09/15/20

I have it in a separate sheet, because I refer to it a LOT. So the formula I gave you is a "cross sheet" formula (it refers to another sheet).

If you wanted to leave it in your same sheet, you'll format the formula a little bit differently.

=INDEX([Month Name]:[Month Name], MATCH(MONTH([Due Date]@row), [Month Number]:[Month Number], 0))

This should get rid of your error.

MCorbin · 09/15/20

There isn't a function that returns the text month, but here's what I do:

I have a sheet that contains a bunch of pieces of Date related info:

So I'll have a formula that looks up the numeric month from your date field:

=MONTH(([Due Date]@row)

Then use that in an Index/Match lookup to come up with the month name:

=INDEX({Month Name}, MATCH(MONTH([Due Date]@row), {Month Number}, 0))

Irina Iatco · 09/15/20

https://community.smartsheet.com/discussion/comment/262755#Comment_262755

Thank you so much for your help. Does this data have to be in a separate or the same sheet? I am obviously not doing something right bc I am getting an error. Please see the screenshot attached.

I used =INDEX({Month Name}, MATCH(MONTH([Due Date]@row), {Month Number}, 0))

Thank you!

MCorbin · 09/15/20

I have it in a separate sheet, because I refer to it a LOT. So the formula I gave you is a "cross sheet" formula (it refers to another sheet).

If you wanted to leave it in your same sheet, you'll format the formula a little bit differently.

=INDEX([Month Name]:[Month Name], MATCH(MONTH([Due Date]@row), [Month Number]:[Month Number], 0))

This should get rid of your error.

Irina Iatco · 09/15/20

https://community.smartsheet.com/discussion/comment/262827#Comment_262827

@MCorbin Thank you so much! You've been so helpful!

Myra Jane Lim · 10/28/20

Thank you @MCorbin! This worked for my sheet. :)

John C Murray · 09/15/23

I know this has be solved already, but there is a simple way to do it with a formula using a static string where each month is padded with spaces.

= SUBSTITUTE( MID( "January February March April May June July August September October November December ", [Month Number]@row * 10 - 9, 10 ), " ", "" )

The month values (including the padding) are all 10 characters long. The MID function determines where the start of the month value is based on the [Month Number], and then extracts all 10 characters. The SUBSTITUTE function then removes the trailing spaces

Excel Text Formula

Best Answer

Answers

Categories