Forum Discussion

Asparagus's avatar
Asparagus
Brass Contributor
Oct 18, 2022
Solved

Is there a way to Auto SPILL cells with 8 letters (characters) not including spaces from a list

I have a list from A2  to A11 with random words, some cells have single words some have more. I would like to grab all the cells with 8 letters/characters (not counting the spaces) and have them spil...
Excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Oct 18, 2022

    Asparagus

    I would suggest

    = LET(
    charCount, LEN(SUBSTITUTE(List," ","")),
    FILTER(List, charCount=8)
  )

    It is obviously possible to substitute charCount in the FILTER formula but, to my mind, the result is less readable despite being shorter.

     

     

dscheikey's avatar
dscheikey
Bronze Contributor
Oct 18, 2022

Asparagus 

Hi! I would guess that you are using the SUBSTITUTE(text, old_text, new_text, [instance_num]) function to exclude the space character when calculating the length. Does that help?

Asparagus's avatar
Asparagus
Brass Contributor
Oct 19, 2022
I'm sorry, though I tried, I can't figure out how to use the formula, provided. I am not sure what to select for the three type of texts and how to type the instance_num. I'm quite new to this type of formula, I've only learnt about LEN, FILTER and SUBSTITUTE today from google searching.

May you add cells for the each section? I think I will be able to follow it better that way.
Thanks so much.

Resources