Forum Discussion

tibbyjudy's avatar
tibbyjudy
Copper Contributor
Mar 18, 2022
Solved

Extract all consecutive 3-letter strings from text cell

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
excel
Formulas and Functions
  • Starrysky1988's avatar
    Starrysky1988
    Mar 18, 2022
    =TEXTJOIN("/",,LOWER(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ",""))-2)),3)))
    **You can change A1 as you need.

2 Replies

  • Starrysky1988's avatar
    Starrysky1988
    Iron Contributor
    Mar 18, 2022
    =TEXTJOIN("/",,LOWER(MID(SUBSTITUTE(A1," ",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1," ",""))-2)),3)))
    **You can change A1 as you need.

Resources