Find / Mid / Len / Right Formula Question

ConnorForm

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!!!

Paul Newcome

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.

Paul Newcome

@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.

Paul Newcome

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.

Cory Page

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.

https://community.smartsheet.com/discussion/66859/pull-text-from-string-using-delimiter

Paul Newcome

@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.

ConnorForm

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!!

Paul Newcome

Happy to help. 👍️

Apparently I need to relearn how to count. 🤣