EXTRACT function: select data between defined tags

There are a couple of other options that can be used to make life a little easier using MID/FIND/SUBSTITUTE combos or UNICHAR characters.

SUBSTITUTE method:

Join all cells on the source sheet using the same delimiter (start and finish the string with that same character to really make life easier). Make sure it is not something that is going to already be included in the data. You are also going to need to pick a second character that won't be included in any of the string data. I will use "!" as the string delimiter and "~" as the second character.

Bring your string over to the target sheet, and then use something along the lines of

=MID(String@row, FIND("~", SUBSTITUTE(String@row, "!", "~", 1)) + 1, FIND("~", SUBSTITUTE(String@row, "!", "~", 2)) - (FIND("~", SUBSTITUTE(String@row, "!", "~", 1))))

That first SUBSTITUTE function replaces the first "!" with "~" making the "~" the only one in the string. That makes it really easy to FIND it and add 1 to establish our starting position for the MID function. The second SUBSTITUTE does the same thing but swaps out the second "!". The FIND function allows us to establish the stopping point. Once we subtract the starting point from the stopping point we have the number of characters for the MID function.

So within the three SUBSTITUTE functions we have 1 / 2 / 1. T. get the second piece from the string we update it to 2 / 3 / 2. The third piece from the string is 3 / 4 / 3, so on and so forth. If it is feasible, you can even use helper cells with references to allow you to use it as a column formula if you are parsing down a column or dragfill if you are parsing across a row by changing the hard coded numbers to

[Number Column]@row / [Number Column]@row + 1 / [Number Column]@row.

.

UNICHAR method:

This is very similar to the method above, but I use this when the string I want to pull together isn't in the same order as the sheet or has extra cells in between to the point where I can't use a JOIN function on the source sheet. Since I can't use the JOIN function and will have to manually input my delimiters anyway, I usually use different ones across the string, but I make it ones that are easy to use.

=UNICHAR(9601) + [First Column]@row + UNICHAR(9602) + [Second Column]@row + UNICHAR(9603)

From there we use the same MID/FIND combo but without needing the SUBSTITUTE function.

=MID(String@row, FIND(UNICHAR(9601), String@row) + 1, FIND(UNICHAR(9602), String@row - (FIND(UNICHAR(9601), String@row) + 1))

You can also use cell references to automate which UNICHAR to look for using a very similar method (same logic).

=MID(String@row, FIND(UNICHAR(9600 + [Number Column]@row), String@row) + 1, FIND(UNICHAR(9600 + [Number Column]@row + 1), String@row - (FIND(UNICHAR(9600 + [Number Column]@row), String@row) + 1))

.

It is still more complex than an EXTRACT function, but it is the cleanest way I have found to meet this particular challenge (so far).

@Lucas Rayala That only works if you know how to type those directly (which I don't). 😅

I personally also like using the UNICHAR because I typically use the [Number Column] cell references to adjust them dynamically so I don't have to manually change the delimiters for every formula. I can either apply it as a column formula or dragfill across a row and be done with it.

@Lucas Rayala - I am trying to implement the solution you listed above but I cannot determine how to build this formula

you could concatenate the data in Column C (using a formula) so it looks like this:

<cA>Cat<\cA><cB>Pizza<\cB>

Smartsheet does not have a CONCATENATE function. In order to use JOIN, Smartsheet does not like me including the <tags> <closing tag>. I need a formula to convert to a column formula. How do I create the formula?

Thank you!

@Gina Smith The EXTRACT function has never existed in Smartsheet. This is a product idea / suggestion for the devs.

To concatenate in Smartsheet without using the JOIN function, you simply "add" the pieces together similar to the UNICHAR method I mentioned above.

@Gina Smith I find the UNICHAR method to be less flexible but easier to work with overall. The SUBSTITUTE method works best for outputting a vertical list and when using a helper column to do so.

UNICHAR method:

This is very similar to the method above, but I use this when the string I want to pull together isn't in the same order as the sheet or has extra cells in between to the point where I can't use a JOIN function on the source sheet. Since I can't use the JOIN function and will have to manually input my delimiters anyway, I usually use different ones across the string, but I make it ones that are easy to use.

=UNICHAR(9601) + [First Column]@row + UNICHAR(9602) + [Second Column]@row + UNICHAR(9603)

From there we use the same MID/FIND combo but without needing the SUBSTITUTE function.

=MID(String@row, FIND(UNICHAR(9601), String@row) + 1, FIND(UNICHAR(9602), String@row - (FIND(UNICHAR(9601), String@row) + 1))

@Maz Uddin Which part are you struggling with?