Special string formatting
 

Am using a excel report generator which works with excel template file
Have a cell in the template file which is referred to as Grid_Value
This cell is populated with a string representing cubic meters o
water i.e
203.34M3
How can the field be formatted in vba to show 203.34 M (superscript)

--
Message posted from http://www.ExcelForum.com

Special string formatting
 

I don't think that you can format a cell to show
super/sub scripted characters. However, you can edit the
text to do thiis, but you'll lose the formula.
Here's a test example which should show how



Sub test()

Range("B2").Value = "2,45M3"

showcubed Range("B2")

End Sub
Function showcubed(ByRef target As Range)
'
'sets the last character to superscript
'
Dim length As Long
length = Len(target.Value)

With target.Characters(Start:=length, length:=1).Font
.Superscript = True
End With

End Function


Patrick Molloy
Microsoft Excel MVP
-----Original Message-----
Am using a excel report generator which works with excel
template file.
Have a cell in the template file which is referred to
as Grid_Value.
This cell is populated with a string representing cubic
meters of
water i.e
203.34M3
How can the field be formatted in vba to show 203.34 M
(superscript)3


---
Message posted from http://www.ExcelForum.com/

.

Special string formatting
 

Hello Christobal
With Range(Grid_Value).Characters(8, 1).Font
.Superscript = True
End With
HTH
Regards
Pascal

"christobal " a écrit dans le
message de news: ...
Am using a excel report generator which works with excel template file.
Have a cell in the template file which is referred to as Grid_Value.
This cell is populated with a string representing cubic meters of
water i.e
203.34M3
How can the field be formatted in vba to show 203.34 M (superscript)3


---
Message posted from http://www.ExcelForum.com/

Special string formatting
 

Sub test()

Range("B2").Value = "2,45M3"

showcubed Range("B2")

End Sub
Function showcubed(ByRef target As Range)
'
'sets the last character to superscript
'
Dim length As Long
length = Len(target.Value)

With target.Characters(Start:=length, length:=1).Font
.Superscript = True
End With

End Function

This is good for a single cell.
The report generator enters numerous rows into an unbound range who
4th column is represented by Grid_Value. The String is of no fixe
lengt

--
Message posted from http://www.ExcelForum.com

Special string formatting
 

christobal wrote in message ...
Am using a excel report generator which works with excel template file.
Have a cell in the template file which is referred to as Grid_Value.
This cell is populated with a string representing cubic meters of
water i.e
203.34M3
How can the field be formatted in vba to show 203.34 M (superscript)3


---
Message posted from http://www.ExcelForum.com/

The ³ of M³ is actually available as a character in its own right
(Check Character map or copy from this message) - therefore suggest
one solution is to define ...
..
Const Mcub As String = " M³"
..
Locate the cell containing your string and then ...
..
ActiveCell.Value = Left(ActiveCell.Value,Len(Activecell.Value)-2) &
Mcub
..
Regards ...

Special string formatting
 

Try format-cells-custom
then in the Type box enter the following:
0.00" M³"
Note that you enter the superscript 3 as Alt 0179 and you must use the
numeric keys of the keypad.
(On a laptop, this may be the only time you ever use the NumLk key)

christobal < wrote:
Am using a excel report generator which works with excel template file.
Have a cell in the template file which is referred to as Grid_Value.
This cell is populated with a string representing cubic meters of
water i.e
203.34M3
How can the field be formatted in vba to show 203.34 M (superscript)3


---
Message posted from http://www.ExcelForum.com/

Special string formatting
 

In VBA:
ActiveCell.NumberFormat = "0.00 ""M³"""
Again, you must use the numeric key pad and Alt0179 while in the VBA
code editor.

christobal < wrote:

Am using a excel report generator which works with excel template file.
Have a cell in the template file which is referred to as Grid_Value.
This cell is populated with a string representing cubic meters of
water i.e
203.34M3
How can the field be formatted in vba to show 203.34 M (superscript)3


---
Message posted from http://www.ExcelForum.com/