Extract a varied length of text from varied positions in a string of text in a cell

Jessica.Rowan

I need to extract this F6MVP1.1 label into the MVP label column to isolate it from the other labels, since I only need to report on the F6MVP label in my pivot. However, the F6MVP label can vary in where it is within the parent labels string and the number of characters can vary too since we will probably extend into the F6MVP1.10+ and beyond so I can't tell Smartsheet to look for F6MVP and return a certain length of characters either because that length will vary as we get into larger numbers. How do I tell Smartsheet to extract the F6MVP label including the numbers up until the comma, no matter where it is in the parent labels string?

Leibel S

@Jessica.Rowan

Try this

=IFERROR(IF(LEN([Parent Labels]@row) < 9, [Parent Labels]@row, MID([Parent Labels]@row, FIND("F6MVP", [Parent Labels]@row), FIND(",", [Parent Labels]@row, FIND("F6MVP", [Parent Labels]@row)) - FIND("F6MVP", [Parent Labels]@row))), "")

Leibel S

Revised:

=IFERROR(IF(AND(LEN([Parent Labels]@row) < 9, CONTAINS("F6MVP", [Parent Labels]@row)), [Parent Labels]@row, MID([Parent Labels]@row, FIND("F6MVP", [Parent Labels]@row), FIND(",", [Parent Labels]@row, FIND("F6MVP", [Parent Labels]@row)) - FIND("F6MVP", [Parent Labels]@row))), "")

Leibel S

@Jessica.Rowan

Try this

=IFERROR(IF(LEN([Parent Labels]@row) < 9, [Parent Labels]@row, MID([Parent Labels]@row, FIND("F6MVP", [Parent Labels]@row), FIND(",", [Parent Labels]@row, FIND("F6MVP", [Parent Labels]@row)) - FIND("F6MVP", [Parent Labels]@row))), "")

Jessica.Rowan

Thanks @Leibel S that worked! Except I'm also getting any other labels that are shorter than 9 characters, but I only want the ones that have F6MVP in them. How do I extend your formula to not show the RPadhhya label below because it's not F6MVP?

Leibel S

Revised:

=IFERROR(IF(AND(LEN([Parent Labels]@row) < 9, CONTAINS("F6MVP", [Parent Labels]@row)), [Parent Labels]@row, MID([Parent Labels]@row, FIND("F6MVP", [Parent Labels]@row), FIND(",", [Parent Labels]@row, FIND("F6MVP", [Parent Labels]@row)) - FIND("F6MVP", [Parent Labels]@row))), "")

Jessica.Rowan

That worked, thanks!

Jessica.Rowan

@Leibel S - After working with this formula, I am noticing one piece still needs to be included. It's extracting out my F6MVP label when it's at the beginning of the list of labels and it's also working when it's in the middle, before a comma, but it doesn't pull it out if it's at the end where there's no comma after it. Can the formula be enhanced further to also extract it if it's at the end where there's no comma after it?

Leibel S

If it is always the same format (starts with F6MVP1. and then has a single digit afterwards) then you can simplify the whole formula to:

=IFERROR(MID([Parent Labels]@row, FIND("F6MVP", [Parent Labels]@row), 8), "")

Jessica.Rowan

@Leibel S the # of digits after the F6MVP can vary (ex. F6MVP1.1 or F6MVP3.15, etc)

Leibel S

@Jessica.Rowan

Try the below:

=IF(FIND(",", [Parent Labels]@row , FIND("F6MVP", [Parent Labels]@row )) > 0, MID([Parent Labels]@row , FIND("F6MVP", [Parent Labels]@row ), FIND(",", [Parent Labels]@row , FIND("F6MVP", [Parent Labels]@row )) - FIND("F6MVP", [Parent Labels]@row )), MID([Parent Labels]@row , FIND("F6MVP", [Parent Labels]@row ), LEN([Parent Labels]@row ) - FIND("F6MVP", [Parent Labels]@row ) + 1))