Formatting 2 strings in cell in different ways

[ardykav]

I have 2 columns, Column A has a regularly formatted first name. Calibri 11. It is also a look up formula from another sheet.

Column B is Surname which is also a lookup formula from another sheet but I have used the Upper function on it to make it all upper case and also bolded the column.

I am wondering is there any way to have all of these in one cell with the formatting preserved?

I suppose the hard part is just getting the bold part on the surname

All help greatly appreciated

 

[CSmith]

have you tried?
=A1 & B1

 

[ardykav]

have you tried?
=A1 & B1

Not sure if you are serious but yes i have thats the problem if you do that the surname does not stay bolded

 

[footoo]

If each cell in columns A:B contains one word only :

Sub v()
Dim Rng As Range, cel As Range
Set Rng = Range("C2:C" & Cells(Rows.Count, "A").End(3).Row)
Rng = Evaluate(Rng.Offset(0, -2).Address & " & "" "" & " & Rng.Offset(0, -1).Address)
For Each cel In Rng
   cel.Characters(InStr(1, cel, " ")).Font.Bold = True
Next
End Sub

 

[ardykav]

thanks but they might have more than one if people have double barrell names and in some cases triple. Is there any way of adpating to just take whatever is in B and Bold it after the merge is done?

If each cell in columns A:B contains one word only :

Sub v()
Dim Rng As Range, cel As Range
Set Rng = Range("C2:C" & Cells(Rows.Count, "A").End(3).Row)
Rng = Evaluate(Rng.Offset(0, -2).Address & " & "" "" & " & Rng.Offset(0, -1).Address)
For Each cel In Rng
   cel.Characters(InStr(1, cel, " ")).Font.Bold = True
Next
End Sub

 

[footoo]

It should work if column B has more than one word but needs revising if column A has more than one.

 

[ardykav]

I think I have a workaround, what if it was 3 Columns, A was first Name only just one word, column B was the other names and then the final column C was the surname to be bolded that may contain 2 words like "De Blanc" ? Would that make it easier?

If each cell in columns A:B contains one word only :

Sub v()
Dim Rng As Range, cel As Range
Set Rng = Range("C2:C" & Cells(Rows.Count, "A").End(3).Row)
Rng = Evaluate(Rng.Offset(0, -2).Address & " & "" "" & " & Rng.Offset(0, -1).Address)
For Each cel In Rng
   cel.Characters(InStr(1, cel, " ")).Font.Bold = True
Next
End Sub

 

[ardykav]

It should work if column B has more than one word but needs revising if column A has more than one.

Yep it would have, would it be better splitting them out into 3 Columns?

 

[Peter_SSs]

With your original formulas in columns A & B, this should put what you want in column C, no matter how many words are in either column.

Sub JoinAndBoldLast()
  Dim c As Range
  
  Application.ScreenUpdating = False
  For Each c In Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
    With c
      .Value = .Offset(, -2).Value & " " & .Offset(, -1).Value
      .Characters(Len(.Offset(, -2).Value) + 2).Font.Bold = True
    End With
  Next c
  Application.ScreenUpdating = True
End Sub

 

[ardykav]

That Sir is outstanding, worked absolutely perfectly thanks so much to both of you for taking the time to write this

With your original formulas in columns A & B, this should put what you want in column C, no matter how many words are in either column.

Sub JoinAndBoldLast()
  Dim c As Range
 
  Application.ScreenUpdating = False
  For Each c In Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
    With c
      .Value = .Offset(, -2).Value & " " & .Offset(, -1).Value
      .Characters(Len(.Offset(, -2).Value) + 2).Font.Bold = True
    End With
  Next c
  Application.ScreenUpdating = True
End Sub