Find / Mid / Len / Right Formula Question
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!!!
Best Answers
-
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 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.
Answers
-
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.
-
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.
-
@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.
-
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!!
-
Happy to help. 👍️
Apparently I need to relearn how to count. 🤣
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives