Hi friend,
​
Welcome to the Google Sheets Tips newsletter #194!

This is a special edition because I have a new formula challenge for you! It's been over a year since the last one, so it's time to dust off your thinking cap and see if you can solve this one.

Formula challenge #6 appears below this week's tip.

​Click here to see previous formula challenges.
​
​➜ News​
​
​I.
​There's a new post on the website exploring People Chips, which let you add additional information and quick action buttons to email addresses in your Sheets.

​How To Create People Chips in Google Sheets

Also, if you're a ConvertKit customer (I use them for these emails), here's how to connect to their API with Apps Script and create an automated daily report:

​Automated ConvertKit Report In Google Sheets​

_______
​
​➜ Google Sheets Tip #194 and Formula Challenge #6: ​
​
Here is your Google Sheets tip, which you can think of as your Monday morning espresso, in spreadsheet form.

Use the MID function to return a segment of a string.

The MID function syntax is

Let's see an example:

Here the MID function extracts the word "Sheets" which starts at position 8 in the original string in cell A1, and has a length of 6 characters.

If you change the length to 3 you get "She" and if you change it to 1, you get the "S" only.

MID is useful in data cleaning exercises, for example when you want to split an invoice number into parts.

It works well when you want to apply the same split dimensions each time, although it can be paired with other text functions like LEFT, RIGHT and LEN to be more flexible.

Formula Challenge #6

Question: can you create a single formula to split a string into separate characters so that each character is in its own cell?

i.e. can you create a single formula in cell B1 that creates the output shown in this example:

BONUS: Formula Challenge #6 Extension

This takes the formula challenge one step further. Proceed if you dare!

Question: can you create a single formula to randomize the characters in a string?

i.e. can you create a single formula in cell B1 to create copy of the string with the letters in random order:

Hints

This week's tip might help you out, e.g. start with this:

and try changing the starting position value (the 2nd argument).

I also used this function and this function in my solution, but I'm sure there are other solutions too ;)

How To Enter

Just hit reply to this email with your formula suggestion. Easy!

I'll share the solution as next week's tip.

_______
​
If you enjoyed this newsletter, please forward it to a friend who might enjoy it.

Have a great week!

Cheers,
Ben

P.S. Just a panda having fun​