Formula to split month and year into two columns

Caroline Elliott

I have data coming into a sheet (Month column) that I need to separate into Month (Helper - Month) and Year (Helper - Year) columns.

I have worked out how to get the month separated using the LEFT function, however when trying to use the RIGHT function to get the Year separated, it works where the month is 4 or less characters only.

This is the formula I am using:

=RIGHT(Month@row, FIND(" ", Month@row))

This is the formula I am using for the Month, which is working as expected:

=LEFT(Month@row, FIND(" ", Month@row))

Any ideas on getting the RIGHT formula to work would be greatly appreciated!

Protonsponge

Hello @Caroline Elliott,

You can use LEN to count the number of characters in a text string and then substitute the characters before the " " in your string.

I think =RIGHT(Month@row, LEN(Month@row) - FIND(" ", Month@row)) will give you what you are looking for and all seems to be working in the demo below.

I hope that is helpful to you in someway,

Protonsponge

Nick Korna

Hi @Caroline Elliott,

Your find is finding the space which doesn't work when you're working backwards. Try this instead:

=RIGHT(Month@row, LEN(Month@row) - FIND(" ", Month@row))

This subtracts the characters up to the space from the total length and so should give you the desired result:

Hope this helps, but if you've any problems/questions, just let us know!

Caroline Elliott

@Protonsponge and @Nick Korna works brilliantly! Thank you so much, I greatly appreciate the help.

Protonsponge

Hello @Caroline Elliott,

You can use LEN to count the number of characters in a text string and then substitute the characters before the " " in your string.

I think =RIGHT(Month@row, LEN(Month@row) - FIND(" ", Month@row)) will give you what you are looking for and all seems to be working in the demo below.

I hope that is helpful to you in someway,

Protonsponge

Nick Korna

Hi @Caroline Elliott,

Your find is finding the space which doesn't work when you're working backwards. Try this instead:

=RIGHT(Month@row, LEN(Month@row) - FIND(" ", Month@row))

This subtracts the characters up to the space from the total length and so should give you the desired result:

Hope this helps, but if you've any problems/questions, just let us know!

Caroline Elliott

@Protonsponge and @Nick Korna works brilliantly! Thank you so much, I greatly appreciate the help.