move text using vba

I'm not exactly sure what you're trying to accomplish, but the following may lead you the right direction:

Say you have someone's first name in cell a1 (Moomal) and their last name in cell b1(Abro). If you want to (at the click of a button you've made) combine those names together (Moomal Abro)then clear the contents of the original cells use the following VBA code in the button's click event:

Range("C1") = (Range("A1") & " " & Range("B1"))
Range("A1") = ""
Range("B1") = ""

Did that help any?

Cory

I need to be able to do two oppostie things.

1) Say a cell contains the text "Moomal Abro". I need to be able to cut "Abro" and paste it into a new cell. This has to be done in such a way that I can find the separating character (like a space or a comma) and use it as the dividing point between the two sections of text. So, the program would check the text "Moomal Abro", find the space, and then cut everthing after the space and paste it into a new cell, so I would then have two cells, one containing the text "Moomal", and the other containing the text "Abro".

2) I need to be able to merge the two cells back together again. So, If I have two cells, one containing the text "Moomal" and the other containing the text "Abro", the program would merge the text together, creating one cell with the text "Moomal Abro", and the two original cells would be gone.

I hope this clarifies what I mean.

Here's some code that I think will do the trick for you:
1)
Sub Separate()
'Written by Barrie Davidson
Dim FirstName As String
Dim LastName As String
Dim Pos As Long

Pos = InStr(1, ActiveCell.Value, " ")
FirstName = Mid(ActiveCell.Value, 1, Pos - 1)
LastName = Mid(ActiveCell.Value, Pos + 1)
ActiveCell.Value = FirstName
ActiveCell.Offset(0, 1).Value = LastName

End Sub

2)
Sub Merge()
'Written by Barrie Davidson
Dim FirstName As String
Dim LastName As String
Dim CompleteName As String

FirstName = ActiveCell.Value
LastName = ActiveCell.Offset(0, 1).Value
CompleteName = FirstName & " " & LastName
ActiveCell.Value = CompleteName
ActiveCell.Offset(0, 1).ClearContents

End Sub

If you have any problems let me know.

Barrie

Thank you, this is exactly what I was looking for.