INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query in excel to combine cells with Superscript and subscript by UDF or Macro
4

Query in excel to combine cells with Superscript and subscript by UDF or Macro

Query in excel to combine cells with Superscript and subscript by UDF or Macro

(OP)
Hi all,
Again came with some query as I tried a lot and not able to get the result.
Question is there are 3 cells in excel with inputs like 20 in 1st cell, +0.2 in 2nd cell, -0.1 in third cell. we need a Macro or UDF to get output in a single cell with 20 and +0.2 as superscript, -0.1 as subscript.
I have tried the below formula which can combine the 3 cells and not resulting with superscript and subscript.
=TEXT(CONCATENATE(O24,IF(P24>0,"+"&P24,"-"&P24),IF(Q24>0,""&Q24,"-"&Q24)),"@") where P24,Q24 and O24 are display cell numbers.
If anyone knows please help.

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Hi,

You'll have to download my workbook solution to make sense of these instructions.

The value is represented in a camera Tool feature.
I have formatted the Camera Tool Picture with a solid line border in RED. You can remove that formatting.
It is linked to O24. The limits are text values linked to P24 & Q24.
You can ADD the Camera Tool to your Quick Access Toolbar(QAT) by
1. Right-click in QAT. Select Customize Quick Access Toolbar...
2. Choose commands from : All Commands
3. Scroll to Camera and Add >> to your QAT
4. OK

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

(OP)
Hi SkipVought,
I have gone through the file. Thanks for your effort and time.
Sorry,the camera option won't work for the requirement as we need output in single cell with +0.2 as superscript and -0.1 as subscript next to 20.

Thanks a lot for the help anyway.

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

The best you'll get in one cell is like...

20+0.02-0.01


I don't believe there's a way to OVERSTRIKE in Excel, which is the technique to use if it were possible.

You would have to interleave, it would appear, the characters of the limits, like

20+-00..0021


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

(OP)
Yes, the number you have written is the exact output looking for.

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Do you know how to do that manually?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro


CODE

Sub ValueTolerance()
'SkipVought 2017 3.14
'target values in O, P,Q in row 24
'SELECT the cell to contain the result
    Dim sVal As String, sMax As String, sMin As String
    Dim p1 As Integer, p2 As Integer
    
    sVal = [O24]
    
    sMax = "+" & [P24]
    
    sMin = "-" & -[Q24]
    
    With ActiveCell
        .Value = sVal & sMax & sMin
    
        p1 = InStr(.Value, "+")
        p2 = InStr(.Value, "-")
        
        With .Characters(Start:=p1, Length:=p2 - p1).Font
            .Superscript = True
            .Subscript = False
        End With
        
        With .Characters(Start:=p2, Length:=Len(.Value) - p2 + 1).Font
            .Superscript = False
            .Subscript = True
        End With
    End With
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

This slight modification places the result in any column on the row where the values in columns O, P & Q are the value, max and min tolerance.

CODE

Sub ValueTolerance()
'SkipVought 2017 3.14
'target values in O, P,Q in row of the ActiveCell
'SELECT the cell to contain the result
    Dim sVal As String, sMax As String, sMin As String
    Dim p1 As Integer, p2 As Integer
    
    With ActiveCell
        sVal = Cells(.Row, "O").Value           'The value
        
        sMax = "+" & Cells(.Row, "P").Value     'The max tolerance
        
        sMin = Cells(.Row, "Q").Value           'The min tolerance
    
        .Value = sVal & sMax & sMin
    
        p1 = InStr(.Value, "+")
        p2 = InStr(.Value, "-")
        
        With .Characters(Start:=p1, Length:=p2 - p1).Font
            .Superscript = True
            .Subscript = False
        End With
        
        With .Characters(Start:=p2, Length:=Len(.Value) - p2 + 1).Font
            .Superscript = False
            .Subscript = True
        End With
    End With
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

(OP)
It works fine. Thank you very much for your time and effort. Really your help is highly appreciated.
Now,I got some idea and will try to do with any row and any column which can select the particular 3 cells required and give the output with superscript and subscript.

Thanks a ton.

Regards,
Ravi

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Skip - that's a very useful little macro, as a demo of formatting strings from VBA as well as for the specific application with sup and subscripts.

I have taken the liberty of amending the code to use the selected range as the input values (with any number of rows). If there are 4 or fewer columns selected the results go in the fourth column, or if more they go in the last column.

Is it OK if I post the code on my blog?

Code edited; added "i as Long" to Dim statement

CODE --> vba

Sub ValueTolerance2()
'SkipVought 2017 3.14
'Ammended to use selected range as input: Doug Jenkins 2017 3.15

'Output to the column to the right of the input range, or to the last column of selected range if NumCols > 4
    Dim sVal As String, sMax As String, sMin As String
    Dim p1 As Long, p2 As Long, NumRows As Long, NumCols As Long, i as Long
    Dim RowVals As Range

    With Selection
        NumRows = .Rows.Count
        NumCols = .Columns.Count
        If NumCols < 4 Then NumCols = 4
        For i = 1 To NumRows
            Set RowVals = .Cells.Offset(i - 1, 0).Resize(1, NumCols)
            
            sVal = RowVals(1, 1).Value           'The value
            
            sMax = "+" & RowVals(1, 2).Value     'The max tolerance
            
            sMin = RowVals(1, 3).Value           'The min tolerance
        
            With RowVals(1, NumCols)
                .Value = sVal & sMax & sMin
        
                p1 = InStr(.Value, "+")
                p2 = InStr(.Value, "-")
            
                With .Characters(Start:=p1, Length:=p2 - p1).Font
                    .Superscript = True
                    .Subscript = False
                End With
                
                With .Characters(Start:=p2, Length:=Len(.Value) - p2 + 1).Font
                    .Superscript = False
                    .Subscript = True
                End With
            End With
        Next i
    End With
End Sub 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Sure. Thanks for asking.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

(OP)
@ Doug Jenkins, I have tried the code posted and getting error "cannot execute code in break mode"

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Ravi, works for me. I just needed to add a declaration for i.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Quote:

I just needed to add a declaration for i.

Oops.

Some background on declaring variables for Ravi:
By default you don't have to declare variables, so if you forget to add "Dim i as long" when you add a new variable i, the code will still work. This is not a good idea though for several reasons, including:
- If you mis-type a variable name the program will treat it as a new variable, rather than giving you an error message
- If anyone copies and pastes your code, and they have settings that require all variables to be declared, the code won't work on their computer.

To require variables to be declared in any code in a module add:
Option Explicit
as the first line in the module.

To get this line automatically added to all new modules when you create them, go to Tools-Options and select "Require Variable Declaration" That will then become the default in all new files.

I had forgotten to change that setting on my new computer.

Your error message "cannot execute code in break mode" indicates that the code is already running, so you can't restart it. Try opening the VB Editor and pressing the F8 key, which will either step through your code, or give you a different error message, indicating what the real problem is.

If you still can't get it to work, upload your spreadsheet and I will have a look.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Ravi, you know that all you have to do is ...

1) select the O,P,Q cells in all the rows that contain data and
2) run the macro

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

(OP)
Thank you both. Really I am learning new things from you. As a design engineer it is a big challenge for me to get results like this in excel. You guys inspired me to learn a lot.
Thanks for your unconditional love :)

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

(OP)
One more thing I found is : If value in any input cell is edited after running the macro, the result will be same as before. Associativity is not there after editing the input cells.

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Quote:

Associativity is not there after editing the input cells.

The problem is that if you want to return a formatted string, then the only way to do it is with a macro, which you will need to re-run every time anything changes.

If you want an active formula (either using standard functions, or VBA user defined functions) you will have to settle for a string that doesn't need special formatting. Something like:
123.45 +0.00/-0.02
could be done, but not if you want subscripts or super scripts.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

You can "automatically" run this macro using the WorkSheet_Change Event.

Plz upload your workbook.


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

(OP)
Hi,

Got the code for superscript and subscript and thought of sharing here.
This macro assumes that you will have 3 cells selected prior to calling it.
Once run, it will ask you to select Target cell for output.


CODE

Public Sub ValueTolerance2()
Dim rngSel As Range, rngTgt As Range
Set rngSel = Selection
'\\ Check if user has selected 3 columns and 1 row
If Not (rngSel.Columns.Count = 3 And rngSel.Rows.Count = 1) Then
    MsgBox "Selection must be 3 columns by 1 row only!", vbInformation
    Exit Sub
Else
    If Application.CountA(rngSel) < 3 Then
        MsgBox "One or more cells are empty!", vbInformation
        Exit Sub
    End If
End If
'\\ Select Range
On Error Resume Next
    Set rngTgt = Application.InputBox(Prompt:="Select Target Cell!", Type:=8)
    Err.Clear
On Error GoTo 0
'\\ Check if user has made valid selection or not.
If Not rngTgt Is Nothing Then
    With rngTgt
        .Value = rngSel.Cells(1, 1).Value & IIf(InStr(rngSel.Cells(1, 2).Value, "+") > 0, rngSel.Cells(1, 2).Value, "+" & rngSel.Cells(1, 2).Value) & _
        IIf(InStr(rngSel.Cells(1, 3).Value, "-") > 0, rngSel.Cells(1, 3).Value, "-" & rngSel.Cells(1, 3).Value)
        p1 = InStr(.Value, "+")
        p2 = InStr(.Value, "-")
        With .Characters(Start:=p1, Length:=p2 - p1).Font
            .Superscript = True
            .Subscript = False
        End With
        With .Characters(Start:=p2, Length:=Len(.Value) - p2 + 1).Font
            .Superscript = False
            .Subscript = True
        End With
    End With
Else
    MsgBox "No cell selected for output!", vbExclamation
End If
End Sub 

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Once again, you need declarations...

CODE

'
    Dim p1 As Integer, p2 As Integer 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro

Ravi, there are all sorts of ways of implementing a concept. Your original question had noting to do with implementation. It was simply,

Quote (Ravi)


Question is there are 3 cells in excel with inputs like 20 in 1st cell, +0.2 in 2nd cell, -0.1 in third cell. we need a Macro or UDF to get output in a single cell with 20 and +0.2 as superscript, -0.1 as subscript....where P24,Q24 and O24 are display cell numbers.

Then you posted...

Quote (Ravi)

One more thing I found is : If value in any input cell is edited after running the macro, the result will be same as before. Associativity is not there after editing the input cells.

I replied...

Quote (SkipVought)

You can "automatically" run this macro using the WorkSheet_Change Event.

Plz upload your workbook

Your feedback regarding how you need to use this macro has been virtually nonexistent.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close