Problem with Superscript in Part of Cell

[lemo]

hi.
having a problem with automating part of the task where the final result in a cell should be two numbers with latter being superscribed.
e.g., in one calculated cell i have -

71% (2%)

where the (2%) should be in superscript.

i tried to write my own function, but to no avail - seems like it doesn't like the fact that only part of the cell is affected.

thanks in advance,
l

 

[Brianwarnock]

Not sure how far you can take this but did you try the .characters(start,length) feature.

This will do a job but maybe not flexible enougth fo your requirements

Brian

With ActiveCell.Characters(Start:=1, Length:=3).Font
        .FontStyle = "Regular"
        End With
    With ActiveCell.Characters(Start:=4, Length:=5).Font
        .FontStyle = "Regular"
        .Superscript = True
    End With

 

[lemo]

thanks Brian.

it takes me as far as it can fix the cells where the cells are values, as opposed to formulas.
which is a problem because my cells are formulas.
still playing with it though, hopefully there is a workaround, although i am pretty pessimistic here.

i am also trying to create a user defined function, but i feel like it won't work on a portion of a cell either.

l

 

[lemo]

Brian, i think i am going to settle with something similar to your suggestion, but i have a question - is it possible to make Start and Length positions dynamic?
i.e., instead of static "4" in "Start:=4" , it will search for the left parenthesis position, etc.

my final goal is to select a range, click a macro button, and watch all the cells with "x% (y%)" format change the latter number, including parenthesis, into superscript.

sorry if this is trivial - i am not good with VBA.

thanks,
l

 

[Brianwarnock]

Yes you can have
Dim s As Integer
Dim l As Integer

.Characters(Start:=s, Length:=l)
and calculate the values for s and l


Brian

 

[Brianwarnock]

Ok I couldn't resist it this should do the trick

Sub formatcells()
Dim s1 As Integer
Dim l1 As Integer
Dim s2 As Integer
Dim l2 As Integer

For Each c In ActiveWindow.RangeSelection
    s1 = 1
    s2 = InStr(c, "(")
    l1 = s2 - 1
    l2 = InStr(c, ")") - l1


With c.Characters(Start:=s1, Length:=l1).Font
        .FontStyle = "Regular"
        End With
    With c.Characters(Start:=s2, Length:=l2).Font
        .FontStyle = "Regular"
        .Superscript = True
    End With
    
Next c
End Sub

Brian
ps You are right of course you do not need to format the first bit

 

[lemo]

tried it first thing this morning - works like a charm..

i am very grateful, Brian, you saved me a week of work..

l

 

[Brianwarnock]

Hi
Glad it worked , but one thing intrigues me, how did you get that info into the cell, if it was by VBA then the formatting could be done at the same time, I think.

Brian

 

[lemo]

no, i tend to avoid VBA, i am a bit VBA-challenged.

got it via the simple formula -
=TEXT(B13,"0%")&" ("&TEXT((B13-B21),"0%")&")"
which pulls the data from pivot tables connected to database. which works for me, generally, except all this fancy formatting i am stuck with.

my other approach was to create a "Superscript" UDF and use it inside the formula, which would be perfect, but, alas, didn't work..

thanks again,
l

 

[qafself]

you can format the cell with a diagonal line and then put a couple of Char(10) in the formula to give you line feeds which will split the information either side of the diagonal line.