Multiple Formats within an Excel Cell 1

[mmogul]

I want to concatenate the data found in two cells and retain the formats in the combined cell in Excel.

Example:

Cell A1: unbold
Cell A2: BOLD

Cell A3: =A1 & A2

The desired result is unboldBOLD where the text "BOLD" is formatted as <bold> but the text "unbold" remains unbolded.

Anyone know how to do this?

 

[AnotherHiggins]

Something like this:

With Range("C1")
    .Value = Range("A1") & " " & Range("B1")
    .Characters(Start:=1, Length:=Len(Range("A1"))).Font.FontStyle = "Regular"
    .Characters(Start:=Len(Range("A1")) + 1, Length:=999).Font.FontStyle = "Bold"
End With

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

It's probably a bit more complex that John's solution, I'm guessing.

Are there more cells than A1 & A2?

If NO then your have your solution, and I bow out.

Does the formatting vary from your example ?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mmogul]

Thanks. Here is what I got working:

For counter = 5 To 24
    Set curCell = Worksheets("Page 1").Cells(counter, 10)
    CountryText = Len(Worksheets("Page 1 Data").Cells(counter + 13, 8))
    lenCurCell = Len(curCell)
    
    curCell.Characters(lenCurCell - CountryText, CountryText + 1).Font.Bold = False
    
    
Next counter

This unbolds the last part of the text in the cell.

 

[SkipVought]

Thanks to a post from GlennUK as a starting point, here's a generalized procedure for this problem that accounts for...

Font
Bold
Italic
Underlined
StrikeThrough
ColorIndex

You can add additional font properties as needed.

Sub test()
    myjoin Range([A1], [A2]), [A3]
End Sub

Sub myjoin(rIN As Range, rOUT As Range)
'thanks to GlennUK @ Tek-Tips for the initial VBA
'SkipVought 2008 DEC 10
    Dim r As Range, i As Integer, j As Integer
'Concatenates the values in the rIN range to the rOUT range
    For Each r In rIN
        rOUT.Value = rOUT.Value & r.Value & " "         'add a SPACE between values
    Next
    rOUT.Value = Left(rOUT.Value, Len(rOUT.Value) - 1)  'remove last SPACE
    
    j = 1
    For Each r In rIN
        For i = 1 To r.Characters.Count
            With r.Characters(i, 1)
                rOUT.Characters(j, 1).Font.Bold = .Font.Bold
                rOUT.Characters(j, 1).Font.Italic = .Font.Italic
                rOUT.Characters(j, 1).Font.Underline = .Font.Underline
                rOUT.Characters(j, 1).Font.Strikethrough = .Font.Strikethrough
                rOUT.Characters(j, 1).Font.ColorIndex = .Font.ColorIndex
            End With
            j = j + 1
        Next
        j = j + 1           'compensate for the SPACE between values
    Next

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Hi Skip,

thanks, very nice. I'll be Archiving that for future use.

====> *

Cheers, Glenn.

Beauty is in the eye of the beerholder.