Macro error - to extract first and second word from a string

Hello All

I have this macro which extracts first word and last word from Column A

Please Login or Register  to view this content.

The macro runs and gives the desired result but also gives run time error 1004 ... Application defined or object defined error.

My requirement is to extract the first and second word from Column A. I got the formula from CPearson website and changed the .Columns(2) formula to get the second word from Column A. Pls note that the formula works but gives run time error 1004 as mentioned above

.Columns(2).Formula = "=TRIM(IF(ISERROR(FIND("" "",A2,1)),A2,MID(A2,FIND("" "",A2,1)+1,IF(ISERROR(FIND("" "",A2,FIND("" "",A2,1)+2)),LEN(A2),FIND("" "",A2,FIND("" "",A2,1)+2))-FIND("" "",A2,1))))"


My other requirement is to have @abc.org added to the value in Columns C.
Eg if the value in Column A John Dyson Project Work
then the macro should display John in Column B and [email protected] in Column C
I tried to add &"abc.org" in the end of Columns(2) formula...but it did not work.

Any help would be greatly appreciated
PRKhan