Why does my MID formula work when I subtract the incorrect length?

Deric

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!

Danielle Arteaga

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.

Danielle Arteaga

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.