SOLVED

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

Brass Contributor

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 spill over into cell C2.

 

I have enclosed an image. The formula that I could find does, includes the space so it spills in cells with 7 letters and 1 space, etc. which is not what I wish. By the letter count the cells with "full stop" and "Lion King" should spill over from cell C2 and "hat boy" should not. Can some one help with the correct formula please? NB: I would like the cell with 8 letters to maintain the space when it spill over, therefore "full stop" will not appear as "fullstop". Thanks

 

Asparagus_0-1666125630159.png

 

 

4 Replies

@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?

best response confirmed by Asparagus (Brass Contributor)
Solution

@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.

PeterBartholomew_0-1666127929724.png

 

 

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.
This worked great! Thank you so very much!
I don't know how you all do it but it is impressive. This helps a lot.
Cheers
1 best response

Accepted Solutions
best response confirmed by Asparagus (Brass Contributor)
Solution

@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.

PeterBartholomew_0-1666127929724.png

 

 

View solution in original post