Text formatting & leading dots

[sbolt6]

Hello Excel Gurus

I am trying to format text with multiple formats and leading dots. I have not been able to figure out if it can be done. I have attached a sample worksheet for reference. If anyone know the trick here, I would be most appreciative.

Thanks in advance!

Scott

 

[SirJB7]

Hi, sbolt6!
Not a custom format, just manually or by VBA.
Regards!

 

[Debraj]

I think,

Custom Number format "@*." and "Roll-off productivity (including compactors)" at the same time in a single cell is not possible, as..
1st one is Display Format & second one is Character Formatting..

In VBA you can do this.. but

* tracking cell's width and populating Number of DOTS
* Decide how many word need to be bold and from where need to start Italic ,​

is highly customized class module level programming.. and that also only Text Manipulation..
recommending to use some WORD processor not Spreadsheet.

 

[bobhc]

Good evening sbolt6

If everything up to the first parenthesis is bold and the first parenthesis and every thing else is regular text formatting then I think those that have sold their souls and use VBA will be able to help you.....

 

[SirJB7]

Hi, sbolt6!

The following code should do the job in the copied cell A13. If I'm not missing anything this used to work in 2007 and 2010 at least, but now it seems to refuse to do it in 2013 version. And it'll be one more reason do downgrade, ha!

Could you give it a try? Or anyone else without 2013. Thank you.

Option Explicit

Sub AnotherDamnedWeirdFormat()
    ' constants
    Const ksWS = "Sheet1"
    Const ksRngS = "A7"
    Const ksRngT = "A13"
    Const ksSep = "("
    ' declarations
    Dim rngS As Range, rngT As Range, r As Range
    Dim I As Integer, J As Integer, A As String
    ' start
    Set rngS = Worksheets(ksWS).Range(ksRngS)
    Set rngT = Worksheets(ksWS).Range(ksRngT)
    ' process
    A = rngS.Value
    I = InStr(A & ksSep, ksSep)
    rngS.Copy rngT
    With rngT
        With .Characters(1, I - 1).Font
            .Bold = True
            .Color = vbRed
        End With
        With .Characters(I, Len(A) - I + 1).Font
            .Bold = False
            .Color = vbGreen
        End With
    End With
    ' end
    Set rngT = Nothing
    Set rngS = Nothing
    Beep
End Sub

Links to everywhere with the same technique:
http://www.mrexcel.com/forum/excel-...ions-multiple-font-types-colors-one-cell.html
http://www.ozgrid.com/forum/showthread.php?t=52863

Regards!

 

[sbolt6]

Thanks to all of you for your advice and suggestions. I copied SirB7's code tried to run it in Excel 2010. What I found was that the entire cell content was bolded again and not just the intended piece. I do appreciate your time and efforts though.

Regards,

Scott