Copying only the first Word of a Cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

For a system I am making, I need a Macro that; copies the first word of the
content of a cell. These words have different lenghts.

For example.
I want the first word of A in Collum B
A B C
1 Mon Do
2 Maar Nee
3 No Way


I tried Recording the action but it failed, misserable.

My Question: "What macro code could I use to copy the first word of collumn
A in Collumn B?"

Thanks In Advance!
 
Dim cLastRow As Long
Dim i As Long
Dim iPos As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To cLastRow
On Error Resume Next
iPos = Application.Find(" ", Cells(i, "A")) - 1
On Error GoTo 0
If iPos = 0 Then iPos = Len(Cells(i, "A"))
Cells(i, "B").Value = Left(Cells(i, "A").Value, iPos)
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top