@PeggyLang When I run into this, I will join multiple cells together with a formula, INDEX/MATCH that over to my target sheet, and then parse it out there. Basically what you are wanting to do but with a couple of extra steps in the middle. I have found that parsing is easier when I use UNICHAR delimiters in a specific order. Something along the lines of

=UNICHAR(8601) + [First Column]@row + UNICHAR(8602) + [Second Column]@row + UNICHAR(8603) + [Third Column]@row + UNICHAR(8604)

Notice my delimiters are UNICHAR functions for 8601, 8602, 8603, and 8604. This makes managing the parsing that much easier.

Now you can use an INDEX/MATCH to bring over this helper column that has the delimited string in it and use this to grab the first.

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

To pull the second piece from the string, change the UNICHAR functions from 1, 2, 1 to 2, 3, 2. The third piece from the string would be 3, 4, 3. The basic idea is that the first UNICHAR is the "piece number" you want to pull. This coincides with the fact that it is in the "character to start with" portion of the MID function. Then to tell the MID function how many characters to pull, we subtract the start of the first piece from the start of the second piece.

So boiled down it would look like this:

=MID([String Column]@row, start of first, start of second - start of first)

And using those specific UNICHAR functions to start, delimit, and end the string makes it much easier to just copy/paste/tweak to get the next piece from the string.

Happy to help. 👍️

I actually use this method quite frequently even if I am not concerned with going over the limit of cross sheet references. If I am pulling over a lot of data from a reference sheet such as entering a store number and returning the address, postal code, country, manager, assistant manager, store type, etc., I will go ahead and use this method to bring it all over into a column called "Store Data" and parse it out. Even if I know I won't hit 100, it drastically cuts down on the number of formulas using cross sheet references which will help with sheet speed on larger and/or more complex sheets, and it looks a little cleaner without having the blue arrows next to so many cells.

@Andrée Starå Yes. But the UNICHAR functions are much easier to update quickly when you are parsing out a lot of fields. I can just change the numbers within the functions, and I know those numbers coincide with which piece I am pulling from my string.

With emojis, I would need to keep a lit up on another screen so that I can see if the smiley face starts my second or third string, and what ends the fourth string etc. etc. and then copy/paste the emojis after copy/pasting the formula itself.

The UNICHAR functions are just a bit easier to use at scale.

@PeggyLang Happy to help. 👍️

Using emojis can be thought of as copy/pasting a tiny picture in between the strings. Instead of just changing a 1 to a 2 or a 2 to a 3, you would need to copy/paste each different tiny picture in. Using the UNICHAR function is much easier to both set up and manage.