Moving, deleting, and concatenating text in cells.

[Guest]

Here is my scenario:
**Column 1** **Column2**
John Doe Jane

And this is what i want it to look like:

**Column1** **Column2**
John & Jane Doe

How would i do that through VBA?
P.S. I have 350 rows that i want to happen to, so manually is something i
really dont want to do. I want to put this in a command button.

Thanks much!

 

[Dave Peterson]

If you have two names (first and last) in column A and first in Column B...

You could use a couple of helper formulas:

=LEFT(A1,SEARCH(" ",A1))&"& "&B1
in C1
and
=MID(A1,SEARCH(" ",A1)+1,255)
in D1
and drag both those formulas down the range.

If you want values, just edit|copy, edit|paste special|values.

(And delete the original columns????)

 

[Guest]

Thanks for the response.

I can get that all to work in Excel. I want to able to do this in VBA code
in Excel, and put the code into a command button. So, it replaces the text
in columns 1 and 2, without having to do copy | paste special after the fact.

Thanks.

 

[Dave Peterson]

With not too much validation...

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim StrA As String
Dim StrB As String
Dim StrANew As String
Dim StrBNew As String
Dim SpacePos As Long

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
With myCell
StrA = Application.Trim(.Value)
StrB = Application.Trim(.Offset(0, 1).Value)

SpacePos = InStr(1, StrA, " ", vbTextCompare)
If SpacePos > 0 Then
StrANew = Mid(StrA, 1, SpacePos) & "& " & StrB
StrBNew = Mid(StrA, SpacePos + 1)
.Value = StrANew
.Offset(0, 1).Value = StrBNew
End If
End With
Next myCell

End Sub

 

[Guest]

Perfect!

Thanks very much, your awesome!

As i am looking at the results, some of the cells are not in the correct
format for this to work for all of them, but it's far better then what i had.

Thank you Dave.