Cut last word in cell and paste in next...

[KYO]

Hi,

I need to select the last word in a cell for a whole column (B) an
paste these
words into Column C...

I am new to this forum and excel and any tips would be greatl
appreciated. :

 

[JulieD]

Hi KYO

i'm assuming you have data such as
blue cat
black dog
green mouse

and you want to see
cat
dog
mouse
in the adjacent column
if so, then in cell C1 type
=RIGHT(B1,LEN(B1)-SEARCH(" ",B1))

then hold your mouse over the bottom right hand corner of the cell until you
see a +, hold the mouse down and drag this formula down column C as far as
necessary.

then click on the column heading to select all the column and copy it
now click on C1 and choose edit / paste special - values
click OK
and you should have the list you require.

Cheers
JulieD

 

[JulieD]

Hi

i've just read your post again and then other alternative is to use data /
text to columns
insert some blank columns to the right of the column you want to split up
then
select your column
choose data / text to columns
choose delimited
untick tab & tick space
click Finish

Cheers
JulieD

 

[Guest]

Try:

Sub xx()
' Or use a function passing in the range as an argument
' i.e. function xx(byval MyRange as range)
For Each Cell In Range("A1:A2")
CurrentCell = Trim(Cell.Value)
Cell.Offset(0, 1) = Mid(CurrentCell, InStrRev(CurrentCell, " "))
Cell.Value = Left(CurrentCell, InStrRev(CurrentCell, " "))
Next
End Sub

Assumes that words are delimited by a space e.g This Example, This is
another example etc.