Applying different formatting to text in formula

[Zaigham]

Excel gives opportunity to apply different formatting to text within a single text. Here is a sample to explain my question. In simple text string XL offer different formatting to various parts of sentence but in a concatenated cell whole string could be formatted e.g., bold, underline or a single colour.
Is there any way to apply different formatting to concatenated text in a formula?
This is a sample of applying different formatting to text.

Excel Workbook
	A
1	This is a sample of applying different formatting to text.
2	*
3	This is a
4	sample of
5	applying different
6	formatting to text.
7	*
8	This is a sample of applying differentformatting to text.
Sheet1

 

[pgc01]

Hi

No. All the characters resulting from a formula will have the same format.

If you need different formats in the text of the cell, the cell must have a constant value, not a formula. Either you enter it manually or use vba to build the result of the concatenation, write the result in the cell as a constant and format it.

 

[Zaigham]

Hi PGC

Can you guide me to a VBA. However, thanks for information.

Regards
Zaigham

 

[Sektor]

[COLOR="Blue"]Sub[/COLOR] FormatChars()
    [COLOR="Blue"]With[/COLOR] Range("B1")
        .Value = Range("A3") & Range("A4") & Range("A5") & Range("A6")
        [COLOR="Blue"]With[/COLOR] .Characters(1, 9) [COLOR="green"]'First 9 characters...[/COLOR]
            .Font.Bold = [COLOR="Blue"]True[/COLOR]
            .Font.ColorIndex = 3
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
        [COLOR="Blue"]With[/COLOR] .Characters(10, 10) [COLOR="Green"]'Next 10 characters...[/COLOR]
            .Font.ColorIndex = 5
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
        [COLOR="Blue"]With[/COLOR] .Characters(20) [COLOR="Green"]'Remained characters...[/COLOR]
            .Font.ColorIndex = 7
        [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]

 

[Zaigham]

Dear Sektor

Thanks for providing VBA. I will try to use this VBA according to my needs. In case of failure I will refer you again.

Regards
Zaigham