Why does my MID formula work when I subtract the incorrect length?
I made typo in one of my formulas but it still returned the desired result. I want to understand so I don't end up with unintended results down the line.
My goal is to extract the data that follows "Set 4:". So in this case the formula should return "0101". It should always return the data that follows "Set 4:". My confusion stems from the "- 5" at the end of the formula. The formula works as long as that final term is greater than or equal to - 5. Please help me understand why this is the case.
Data in column FUND:
[Set 1: 001
Set 2: 003
Set 3: 009
Set 4: 0101]
Formula:
=IFERROR(MID(FUND@row, FIND("Set 4:", FUND@row) + 6, LEN(FUND@row) - FIND("Set 4:", FUND@row) - 5), "")
I know i can use RIGHT here, but I was using MID with the other values. Please let me know if there is a compelling reason to use RIGHT or some other function.
Thank you!
Best Answer
-
The basic syntax for the MID formula is
MID(text, start_position, num_chars). Start_position is calculated starting from the leftmost text. It starts at the first character unless you specify num_chars; then counting starts from left at that position.
Here, your "text" is the text in the FUND column at the specific row.
Your "start_position" is FIND("Set 4:", FUND@row) + 6. FIND calculates the starting position in a text string at a specific location. So, your FIND formula determines whether the row contains "Set 4:" and returns the starting position of where that text is found, which is 1 (because there is no text preceding "Set 4:"). You then add 6 to that result, so your starting position becomes character 7 of the row.
Your "num_chars" is LEN(FUND@row)-FIND("Set 4:", FUND@row)-5. LEN calculates the number of text characters in the row (which, for "Set 4: 0101" is 11) less the starting position of "Set 4:" in the FUND row (which we know, from above is 1) less 5 characters. So, 11-1-5=5.
So, the formula tells Smartsheet to return the 5 characters in a row that contains "Set 4:" starting at character 7 (counting from left). Character 7 in the sequence "Set 4: 0101" is the space between the colon and 0. There are only 4 characters that follow, so SS returns "0101" even though you specified 5. Specifying 5 will enable your formula to work if your "Set 4:" values exceed 4 characters (e.g., 01011).
I hope this is helpful. If your Set 4 sequences won't exceed 5 characters following the colon, I don't see any reason to change this from MID to RIGHT.
Answers
-
The basic syntax for the MID formula is
MID(text, start_position, num_chars). Start_position is calculated starting from the leftmost text. It starts at the first character unless you specify num_chars; then counting starts from left at that position.
Here, your "text" is the text in the FUND column at the specific row.
Your "start_position" is FIND("Set 4:", FUND@row) + 6. FIND calculates the starting position in a text string at a specific location. So, your FIND formula determines whether the row contains "Set 4:" and returns the starting position of where that text is found, which is 1 (because there is no text preceding "Set 4:"). You then add 6 to that result, so your starting position becomes character 7 of the row.
Your "num_chars" is LEN(FUND@row)-FIND("Set 4:", FUND@row)-5. LEN calculates the number of text characters in the row (which, for "Set 4: 0101" is 11) less the starting position of "Set 4:" in the FUND row (which we know, from above is 1) less 5 characters. So, 11-1-5=5.
So, the formula tells Smartsheet to return the 5 characters in a row that contains "Set 4:" starting at character 7 (counting from left). Character 7 in the sequence "Set 4: 0101" is the space between the colon and 0. There are only 4 characters that follow, so SS returns "0101" even though you specified 5. Specifying 5 will enable your formula to work if your "Set 4:" values exceed 4 characters (e.g., 01011).
I hope this is helpful. If your Set 4 sequences won't exceed 5 characters following the colon, I don't see any reason to change this from MID to RIGHT.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!