On Mon, 7 Nov 2005 03:30:57 -0600, NITESH G
wrote:


Hi,
how to format a text in concatenate function.
for eg. "mm2" . i want to do in concatenate function ,
the last no. 2 should be in superscript position
Pls suggest or any alternative

If your superscripts will be either a 2 or a 3, then Roger's solution of
entering mm2 by using <alt-0178 (or <alt-0179 for a 3) will work.

mm² or mm³

That works because those superscripted numbers are part of the default font.

However, if you want that value to be retained as a value, or if your
requirements are for more than just the numbers 2 or 3 to be superscripted,
then you will require a VBA solution that both converts your formula into a
text string, and then superscripts that particular number. There is no way,
other than as part of a text string, to differentially format a portion of the
string. And the string has to be a true string, and not the output of some
other formula.

For example, this SUB will superscript numbers that immediately follow any of
several defined (in A) short strings:
--------------------------
Option Explicit

Sub Super()
Dim c As Range
Dim A
Dim i As Long, j As Long
Dim SSNum As Long, SSLen As Long

A = Array("mm", "cm", "km")

For Each c In Selection
For i = 0 To UBound(A)
If c.Text Like "*" & A(i) & "#*" Then
SSNum = InStr(1, c.Text, A(i)) + Len(A(i))
j = SSNum
Do While IsNumeric(Mid(c.Text, j, 1))
j = j + 1
Loop
SSLen = j - SSNum
With c
.Value = .Text
.Characters(SSNum, SSLen).Font.Superscript = True
Exit For
End With
End If
Next i
Next c

End Sub
------------------------

To enter this sub, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explore window, then Insert/Module and paste the
code above into the window that opens.

To use the sub, first select the cell(s) you wish to operate on. Then <alt-F8
opens the Macro Dialog Box. Select Super and RUN.


--ron