# Find / Mid / Len / Right Formula Question

Options
✭✭✭✭

Good Afternoon All,

I feel like I am close but just might need a little assitance to finish this formula. However I am trying to pull the "Month_Year" from these text strings I keep getting "Incorrect Argument erros when I try to put this in. I think it is because some of these text strings are longer than others or other reasons so looking to see if I can get a more consistent/accurate formula down. Any tips or pointers are welcome!!! Just to note: "CX-50" will not always be CX-50, this can change so i couldn't base it off that value

Thanks for the time!!!

Tags:

• ✭✭✭✭✭✭
Options

Is it safe to assume it will always start immediately after the 6th "_" and be 8 characters long (always "MMM_YYYY")? If so, you can use this:

=MID([Column Name]@row, FIND("!", SUBSTITUTE([Column name]@row, "_", "!", 6)) + 1, 8)

There are other options if the number of underscores before it is variable, but if they aren't, this is one of the more efficient ways to grab it.

• ✭✭✭✭✭✭
Options

@Cory Page Your method only works if the number 4 is not present earlier in the string (which it is in some of the sample data provided in the screenshot) and if the year ends in the number 4. Take a look at the solution I posted above.

It is also a good example of the SUBSTITUTE method that is suggested later in the thread because my initial solution had been made obsolete.

• ✭✭✭✭✭✭
Options

Is it safe to assume it will always start immediately after the 6th "_" and be 8 characters long (always "MMM_YYYY")? If so, you can use this:

=MID([Column Name]@row, FIND("!", SUBSTITUTE([Column name]@row, "_", "!", 6)) + 1, 8)

There are other options if the number of underscores before it is variable, but if they aren't, this is one of the more efficient ways to grab it.

• ✭✭✭✭✭
Options

This may not be a good answer but its pretty simply and you could always add complexity if you want. in this case I am searching for the unique value "4_" then subtracting 8 to find the starting point. It seems like your data set has a set character limit for the month and year so this should work. If you don't want the _ returned just change -8 to -7.

=MID([Name Column ID]1, SUM(FIND("4_", [Name Column ID]1) - 8), 9)

Paul also did a really great breakdown in case you want something a bit different. Hope this helps.

• ✭✭✭✭✭✭
Options

@Cory Page Your method only works if the number 4 is not present earlier in the string (which it is in some of the sample data provided in the screenshot) and if the year ends in the number 4. Take a look at the solution I posted above.

It is also a good example of the SUBSTITUTE method that is suggested later in the thread because my initial solution had been made obsolete.

• ✭✭✭✭
Options

Thank you @Paul Newcome that formula worked! I just had to change it from 6 to 7!!! But it is working!! ALso thank you @Cory Page for assistance as well!!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

Apparently I need to relearn how to count. 🤣