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, 1115=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, 1115=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
Check out the Formula Handbook template!