Merging Cell Information

[philb99]

Col Col A Col B

First name	Last name
Mrs	Brown, Lisa
Tom	Smith
Fred	Jones
Mr	Delph, Clive
Stuart	Round

<tbody>
</tbody>

Any idea how I would merge the two columns in order that I can read this as Mrs Lisa Brown / Tom Smith / Fred Jones / Mr Clive Delph / Stuart Round in one cell please

 

[JoeMo]

Here's a UDF you can try. Once installed, it can be used like a worksheet function as in the example below.
Sheet2

	A	B	C
1	First name	Last name
2	Mrs	Brown, Lisa	Mrs Lisa Brown
3	Tom	Smith	Tom Smith
4	Fred	Jones	Fred Jones
5	Mr	Delph, Clive	Mr Clive Delph
6	Stuart	Round	Stuart Round

Spreadsheet Formulas

Cell Formula C2 =philb99(A2:B2)

Function philb99(R As Range) As String
If InStr(R.Cells(1, 2), ", ") > 0 Then
    philb99 = R.Cells(1, 1) & " " & Split(R.Cells(1, 2), ", ")(1) & " " & Split(R.Cells(1, 2), ", ")(0)
Else
    philb99 = R.Cells(1, 1) & " " & R.Cells(1, 2)
End If
End Function

Do you really want it all in one cell?? If so, add the second function below:

Excel Workbook
	A	B	C	D
1	First name	Last name		All in one cell
2	Mrs	Brown, Lisa	Mrs Lisa Brown	Mrs Lisa Brown/Tom Smith/Fred Jones/Mr Clive Delph/Stuart Round
3	Tom	Smith	Tom Smith
4	Fred	Jones	Fred Jones
5	Mr	Delph, Clive	Mr Clive Delph
6	Stuart	Round	Stuart Round
Sheet2

Function EasyConcat(R As Range, Optional Delim As String)
If R.Count < 2 Or R.Areas.Count > 1 Then
    EasyConcat = CVErr(xlErrNA)
    Exit Function
End If
If R.Rows.Count = 1 Then
    EasyConcat = Join(Application.Index(R.Value, 1, 0), Delim)
    Exit Function
ElseIf R.Columns.Count = 1 Then
    EasyConcat = Join(Application.Transpose(R.Value), Delim)
    Exit Function
End If
EasyConcat = CVErr(xlErrNA)
End Function

 

[jondavis1987]

I believe he meant have the code merge it all into one column that looks like your column C, and gets rid of column A and B

 

[jondavis1987]

Basically a macro that will do this formula

=IFERROR(A1&" "&RIGHT(B1,FIND(",",B1)-1)&" "&LEFT(B1,FIND(",",B1)-1),A1&" "&B1)

and then delete the other columns

 

[Scott T]

Basically a macro that will do this formula

=IFERROR(A1&" "&RIGHT(B1,FIND(",",B1)-1)&" "&LEFT(B1,FIND(",",B1)-1),A1&" "&B1)

and then delete the other columns

You are missing a step. One would need to copy and past as values so that it is no longer a formula.

 

[jondavis1987]

Sub Merge()'
' Merge Macro
'


'
    Range("C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(RC[-2]&"" ""&RIGHT(RC[-1],FIND("","",RC[-1])-1)&"" ""&LEFT(RC[-1],FIND("","",RC[-1])-1),RC[-2]&"" ""&RC[-1])"
    Range("C1").Select
    Columns("C:C").EntireColumn.AutoFit
    Selection.AutoFill Destination:=Range("C1:C5"), Type:=xlFillDefault
    Range("C1:C5").Select
    Range("C1:C5").Select
    Selection.Copy
    Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1:B5").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("A:A").EntireColumn.AutoFit
End Sub

 

[jondavis1987]

This would make the formula happen in Column C, repaste it as Values, Delete Columns A & B. You would need to change the ranges to what you need

 

[Joe4]

Jon,

I would strongly caution you not to use reserved words like "Merge" as the name of Procedures, Functions, or Variables.
Reserved words are words that are already used by Excel in functions, methods, properties, etc.

Using a reserved word can lead to ambiguity and errors on Excel's part (it doesn't know whether you are referring to the "Merge" method, or the "Merge" procedure).
Since it can be hard to always know if you are using a reserved word or not, one trick is to prefix every with something like "My", i.e. "MyMerge".

 

[philb99]

Thanks Jo - yes just looking for Column C set up

Works a treat Many thanks