formatting before concatenation
 

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
..

formatting before concatenation
 

Ivan


=TEXT(A1,".000")&"footnote" or
=CONCATENATE(TEXT(A1,".000"),"footnote")

Tony
-----Original Message-----
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
..

.

formatting before concatenation
 

Hello,
use FIXED for e.g.

=FIXED(<Range or <Value to Format,3)

Heiko
:-)

"ivan" wrote:

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
.

formatting before concatenation
 

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
----------------


"ivan" wrote in message ...
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
.