Forum Discussion
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 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
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.
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.
- 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
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?
- 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.
