I found an old Post from Aladin and adapted.
Aladin or HotPepper, if you guys come along and can spare a moment and can explain how this works... I am tying this into the last formula explanation.
I just cannot seem to get my head around how to go about thinking about this correctly to get to understand it.
=LEN(TRIM(RIGHT(" "&Y6,LEN(" "&Y6)-SEARCH("#",SUBSTITUTE(" "&Y6," ","#",LEN(" "&Y6)-LEN(SUBSTITUTE(" "&Y6," ","")))))))
=TRIM(RIGHT(" "&Y6,LEN(" "&Y6)-SEARCH("#",SUBSTITUTE(" "&Y6," ","#",LEN(" "&Y6)-LEN(SUBSTITUTE(" "&Y6," ",""))))))