formatting before concatenation

  • Thread starter Thread starter ivan
  • Start date Start date
I

ivan

Hello,
Here is my quandry: I have a data table that imports
information from another part of my worksheet. I want to
import also some footnotes (superscripted) into the same
cell (this suggests concatenation to me). The problem is
that my original data contains anywhere from 0 to 3 decimal
places and in the table it _MUST_ be represented as 3.
Normally a simple formatting of the cells would work, _BUT_
when I add the concatenation command, it ignores the
formatting rule and instead prints the whole thing.

examples:
this is what I want:
10.000footnote (original data is 10)
12.300footnote (original data is 12.3)
13.660footnote (original data is 13.66)
18.777footnote (original data is 18.777)

this is what I get:
10footnote
12.3footnote
13.66footnote
18.777footnote

so it only works when 3 decimals are given...

help please.
thanks,
Ivan
..
 
Copy this function into a code module and use in your workshet as normal
eg. =SPLITx(A1) :-

'=======================================
Public Function SPLITx(st As String)
For c = 1 To Len(st)
If Asc(Mid(st, c, 1)) > 64 Then
Exit For
End If
Next
SPLITx = Format(Left(st, c - 1), "#.000")
End Function
'========================================

Regards
BrianB
 
Back
Top