SOLVED

Extract all consecutive 3-letter strings from text cell

New Contributor

I need a formula to extract all consecutive 3-letter strings from cells with varying lengths of text, then aggregate them, separated by slashes. In the example below, the column on the left is the data I'm starting with, and the column on the right is what I need to end up with. This example was done manually. 

Ainsworthain/ins/nsw/swo/wor/ort/rth
Bandonban/and/ndo/don
Devils Lakedev/evi/vil/ils/lsl/sla/lak/ake
2 Replies
best response confirmed by tibbyjudy (New Contributor)
Solution
=TEXTJOIN("/",,LOWER(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ",""))-2)),3)))
**You can change A1 as you need.
Thank you! Works perfectly.