Need Macros to Extract First Two Words and Last Word

[madhuchelliah]

Hey Guys. I need a macro to extract contents before 1st space i.e 1st word, likewise 2nd space i.e 2nd word and contents after the last space i.e last word of the cell. My source data is in I column from I2 cell and so on. Now macros should extract 1st word to C2, 2ndword to D2 cell and the last word which is after the last space should be in H2 cell. Please help Thank you.

 

[Rick Rothstein]

Give this macro a try...

Sub FirstTwoAndLastWords()
  Dim R As Long, LastRow As Long, Words() As String
  LastRow = Cells(Rows.Count, "I").End(xlUp).Row
  For R = 2 To LastRow
    Words = Split(Cells(R, "I").Value)
    Cells(R, "C").Value = Words(0)
    Cells(R, "D").Value = Words(1)
    Cells(R, "H").Value = Words(UBound(Words))
  Next
End Sub

 

[stunnrock]

Is it important to use a macro?

C2: =LEFT(I2,FIND(" ",I2)-1)
D2: =MID(I2,LEN(C2)+2,FIND(" ",I2,LEN(C2)+2)-LEN(C2)-2)
H2: =RIGHT(I2,LEN(I2)-FIND("¬",SUBSTITUTE(I2," ","¬",SUMPRODUCT(--(MID(I2,ROW(INDIRECT("R1:R"&LEN(I2))),1)=" ")))))

 

[Rick Rothstein]

Is it important to use a macro?

C2: =LEFT(I2,FIND(" ",I2)-1)
D2: =MID(I2,LEN(C2)+2,FIND(" ",I2,LEN(C2)+2)-LEN(C2)-2)
H2: =RIGHT(I2,LEN(I2)-FIND("¬",SUBSTITUTE(I2," ","¬",SUMPRODUCT(--(MID(I2,ROW(INDIRECT("R1:R"&LEN(I2))),1)=" ")))))

The OP asked specifically for a macro, but if it turns out that a formula solution is okay to use, then I would suggest replacing your suggested formula for cell H2 with this simpler one...

=TRIM(RIGHT(SUBSTITUTE(I2," ",REPT(" ",300)),300))

Here the assumption is that the cells in Column I will never exceed 300 characters (if they could, then both 300s should be changed to a number larger than the longest possible text string that could occur. If a maximum length cannot be established, the both 300s should be replaced by LEN(I2).

 

[stunnrock]

I like it. In fact, with that formula, the value only needs to be larger than the length of the last word. However, if the original text had a trailing space it would fail. So, slight amendment to allow for this:

=TRIM(RIGHT(SUBSTITUTE(TRIM(I2)," ",REPT(" ",50)),50))

 

[madhuchelliah]

Hi Rick, I am getting in this line "Cells(R, "C").Value = Words(0)". Please look into this. Thank you

 

[madhuchelliah]

Hello Rick, could you please help me to delete the extracted words from the I column. I tried as much i can. I can do it with formulas only. I cant code macros successfully. Please help me to do this with macro.