MrExcel Publishing
Your One Stop for Excel Tips & Solutions

move text using vba


Posted by Moomal Abro on June 15, 2001 10:30 AM

I am trying to figure out how to use excel VBA to move part of the text of one cell to a new cell (removing the original text), and how to merge the text of two cells back together again. I think it should be fairly simple, but I'm new to VBA and I can't find any refernece material that deal with this topic specifically. I would appreciate any help.


Posted by Cory on June 15, 2001 10:54 AM

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

Posted by Moomal Abro on June 15, 2001 11:04 AM

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.

Posted by Barrie Davidson on June 15, 2001 11:36 AM

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

Posted by Moomal Abro on June 15, 2001 11:39 AM

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