Formula to pull constant number string from variable text strings

@ljpettr1

I'm glad it was helpful! 😁

If there's anything else you need, just let me know.

@Lucas Rayala I thought about the "space 20" as well, but there is an entry that indicates "11 ..... kits". If that number of kits happened to be 20, then the logic would grab pieces of string that we don't want. That's why I was looking at the "SO space" as the indicator.

I always suggest a consistent but unique indicator. Consistent in that it comes immediately before each instance that needs parsed out, and unique in that the only time that indicator appears in the string is when there is something to be parsed out.

From there I use the MID/SUBSTITUTE method to grab 1st, 2nd, 3rd, etc. entry from the string the same as if we had a list where every piece gets parsed out such as from a multi-select dropdown. The logic and formulas are still the same. In this particular instance though, we know the character count is a set number, so that simplifies the MID/SUBSTITUTE method even further.

=MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "SO ", "~", 1)) + 1, 9)

The "~" shouldn't ever be found anywhere in the text. You can use whatever character you want. I have even substituted out with a UNICHAR function of some way off the wall symbol that I know will never be found in the string.

The first 1 (the one inside of the SUBSTITUTE function) tells you to pull the 1st set. Change that to a 2 for the 2nd set, so on and so forth.

With the variable number of sets to pull, I would suggest getting it up and running and then wrapping it in an IFERROR so that cells are left blank when there aren't that many sets to pull.

@ljpettr1 The MID function allows us to pull characters out of a text string based on a starting point and number of characters. Usually when trying to locate a variable starting point based on a consistent indicator, we use the FIND function which will output a number based on where a specific character was found within a string.

For example, if we have the string "ABCDEFGHIJKLMNOP" and we use the FIND function to look for the letter "C", it will output a 3.

=FIND("C", "ABCDEFGHIJKLMNOP")

So if we wanted to pull 5 characters starting with the letter "C" but the letter "C" could be anywhere within the string, we would use

=MID("ABCDEFGHIJKLMNOP", FIND("C", "ABCDEFGHIJKLMNOP"), 5)

The challenge comes when there are multiple instances of the letter "C" that need to be pulled. In that case we still use the FIND function, but we leverage the SUBSTITUTE function because it allows us to specify which instance fo the letter "C" we want to substitute.

What we can do is use the SUBSTITUTE function to replace the first "C" with something else that won't be found in the text string such as "~". Now we can FIND the "~" and know the starting point of our first piece to pull.

Then we can slightly tweak that same formula and change the 1 in the SUBSTITUTE function telling it to replace the first "C" to a 2 to tell it to replace the second "C". Now when we FIND the "~", it will tell us the location of our second "C".

We also know that the FIND function will output the location of the first character if we are telling it to find a string.

=FIND("CDE", "ABCDEFGHIJKLMNOP")

will output 3 because that's where the string we told it to find starts, but because we included extra characters we know it will skip over instances of "C" being by itself or followed by other characters.

We can roll all of these pieces together to SUBSTITUTE the nth "200" with a "~", FIND the "~", and then use that as the starting point of the MID function.

=MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "200", "~", 1), 9)

Then the second 9 character string that starts with "200" would be

=MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "200", "~", 2), 9)

And the third...

=MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "200", "~", 3), 9)

So on and so forth. You would pre-populate these formulas into a series of helper columns. To keep it from throwing an error in the 3rd column if there are only two SO #s, we would wrap each on in an IFERROR.

=IFERROR(MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, "200", "~", 1), 9), "")

.

Now for the "tricky" part. You say that some could start with "200" and others could start with "201". I am sure that we could write out some messy formula that can roll both into a single set of columns, but honestly the easiest way to build and manage this would be to have a set of columns that pull the "200" numbers and a second set of columns that pull the "201" numbers. There are a couple of options for getting rid of the empty cells in between that we can explore, but that will require a few more questions. I would suggest getting this part up and running first before trying to tackle that piece.

@Paul Newcome -- this is an awesome solution. I never thought of using the substitute like this, so I've always had a complicated (and glitchy) "FIND" function columns 2, 3, etc, that looked back to the previous columns and used those inputs to FIND the instance after that. Total mess, this is so much cleaner. This needs to be a best practice article, if it's not already. Bookmarking!