Oct 18 2022 01:42 PM
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
Oct 18 2022 02:06 PM
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?
Oct 18 2022 02:19 PM
SolutionI 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.
Oct 18 2022 05:57 PM
Oct 18 2022 06:01 PM
Oct 18 2022 02:19 PM
SolutionI 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.