Copy/Paste from once cell to another

[ebraun01]

Is there a way to create a macro or formula that will copy just the first word of a cell and past it into a new cell? To take that further, copy the first word out of each cell in a column and past those into cells within a new column?

[Don Guillett]

formula to copy down
=LEFT(F11,FIND(" ",F11)-1)

--
Don Guillett
SalesAid Software
[email protected]
"ebraun01" <[email protected]> wrote in
message news:[email protected]...
>
> Is there a way to create a macro or formula that will copy just the
> first word of a cell and past it into a new cell? To take that
> further, copy the first word out of each cell in a column and past
> those into cells within a new column?
>
>
> --
> ebraun01
> ------------------------------------------------------------------------
> ebraun01's Profile:
> http://www.excelforum.com/member.php...o&userid=30340
> View this thread: http://www.excelforum.com/showthread...hreadid=504474
>

[ebraun01]

Perfect, works great. Now is there a way to tell it when to run at a time of my choosing. In other words I would prefer to not just have the formula within the cells but to hit a button or run a macro and have the formula ran.

[Don Guillett]

Sub getfirstword()
lr=cells(rows.count,"f").end(xlup).row
For Each c In Range("f9:f" & lr)
offset(, 1) = Left(c, InStr(c, " ") - 1)
Next
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"ebraun01" <[email protected]> wrote in
message news:[email protected]...
>
> Perfect, works great. Now is there a way to tell it when to run at a
> time of my choosing. In other words I would prefer to not just have the
> formula within the cells but to hit a button or run a macro and have the
> formula ran.
>
>
> --
> ebraun01
> ------------------------------------------------------------------------
> ebraun01's Profile:
> http://www.excelforum.com/member.php...o&userid=30340
> View this thread: http://www.excelforum.com/showthread...hreadid=504474
>

[ebraun01]

Thanks Don, I really appreciate your great help.

That looks like to much information for a cell formula..where does that go?

[Don Guillett]

You did ask for a macro, didn't you? use alt f11 to put into a module and
then assign to a button or run from alt f8

--
Don Guillett
SalesAid Software
[email protected]
"ebraun01" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Don, I really appreciate your great help.
>
> That looks like to much information for a cell formula..where does that
> go?
>
>
> --
> ebraun01
> ------------------------------------------------------------------------
> ebraun01's Profile:
> http://www.excelforum.com/member.php...o&userid=30340
> View this thread: http://www.excelforum.com/showthread...hreadid=504474
>

[ebraun01]

Your right, I did ask for a macro.. must have taken my stupid pill that day.

I created a macro and ran it and received a compile error. "sub or function not defined" with "Offset(, 1) =" highlighted in blue. Do I need to put something after the = but before the word "left"?

[Don Guillett]

should be
c.offset

Sub getfirstword()
lr=cells(rows.count,"f").end(xlup).row
For Each c In Range("f9:f" & lr)
c.offset(, 1) = Left(c, InStr(c, " ") - 1)
Next
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"ebraun01" <[email protected]> wrote in
message news:[email protected]...
>
> Your right, I did ask for a macro.. must have taken my stupid pill that
> day.
>
> I created a macro and ran it and received a compile error. "sub or
> function not defined" with "-Offset(, 1) =" -highlighted in blue. Do I
> need to put something after the = but before the word "left"?
>
>
> --
> ebraun01
> ------------------------------------------------------------------------
> ebraun01's Profile:
> http://www.excelforum.com/member.php...o&userid=30340
> View this thread: http://www.excelforum.com/showthread...hreadid=504474
>