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.
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
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,
Just traded in my OLD subtlety...
for a NUance!
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
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
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,
for a NUance!
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
Skip,
Just traded in my OLD subtlety...
for a NUance!
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 SubSkip,
Just traded in my OLD subtlety...
for a NUance!
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 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 SubSkip,
Just traded in my OLD subtlety...
for a NUance!
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
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
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 SubDoug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
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
1) select the O,P,Q cells in all the rows that contain data and
2) run the macro
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
Thanks for your unconditional love :)
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
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
Plz upload your workbook.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
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 SubRE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
CODE
' Dim p1 As Integer, p2 As IntegerSkip,
Just traded in my OLD subtlety...
for a NUance!
RE: Query in excel to combine cells with Superscript and subscript by UDF or Macro
Then you posted...
I replied...
Your feedback regarding how you need to use this macro has been virtually nonexistent.
Skip,
Just traded in my OLD subtlety...
for a NUance!