Strings, counting and moving words

**rs585832** · 07-21-2011, 03:30 PM

I have created a macro that counts the words in a cell now i want the macro to cut a certain part of the sentence out and paste it in anther cell

for example I want cell N5 which contains the original text (which can be different at times) and i want a macro to take the 7th word and onward and paste it to cell N6

heres what i have so far to count the words and what i tried to create an if statement to take the 7th word and onward to paste to cell n6

Please Login or Register  to view this content.

THANKS!!!!!! please let me know if i can clarify anything

**Bob Phillips** · 07-21-2011, 03:35 PM

You don't need VBA

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),6))+1,99)

**rs585832** · 07-21-2011, 03:50 PM

Thanks for the reply! I am almost there just one problem! is there anyway you can delete the text from the original cell that you copied to the new cell

like cell A1 has some text "did the dog walk far from the park today"
your formula in cell A2 "the park today"

now can you delete the "the park today" from cell A1?

**Leith Ross** · 07-21-2011, 04:58 PM

Hello ,

Here is robust version of your macro. Leading and trailing spaces are ignored, and multiple spaces are counted as one. If there are not at least 7 words in the string then the macro leaves it unchanged.

Please Login or Register  to view this content.

**Bob Phillips** · 07-21-2011, 05:14 PM

Originally Posted by rs585832

Thanks for the reply! I am almost there just one problem! is there anyway you can delete the text from the original cell that you copied to the new cell

like cell A1 has some text "did the dog walk far from the park today"
your formula in cell A2 "the park today"

now can you delete the "the park today" from cell A1?

You need to do a Copy>Paste Values on A2 then clear A1.