Hmm Im using 2002 SP3, Ill check on my home machine and see if that
helps as I am sure I am running latest version there.

Cheers,

Aaron.
Gord Dibben wrote:
Trevor's formula works just fine for me in Excel 2003.

Tested up to 6 DP

Note: the extraneous "-" in FIN-D can come from the trip through google posting.


Gord Dibben MS Excel MVP


On 12 Oct 2006 15:45:49 -0700, "Slashman" wrote:

Hi Trevor,

It doesent work for me.

I still get the same result.

One strange thing I noticed, is if you copy and paste your formula, the
last FIND command pastes as FIN-D have you noticed that? Copy and paste
the non quoted text though.

I have just tried again to copy and paste (using the quoted text) and I
get the text now:

This weight 123 is in g regardless of A1 actual DP.

Do I need to format my cells special or something?

Cheers for your ongoing help,

Aaron.




Trevor Shuttleworth wrote:
300 This weight 300 is in g
300.1 This weight 300.1 is in g
300.12 This weight 300.12 is in g
300.123 This weight 300.123 is in g
300.1234 This weight 300.1234 is in g

Using:

="This weight
"&INT(A1)&IF(ISERROR(FIND(".",A1)),"","."&MID(A1,F IND(".",A1)+1,LEN(A1)-FIND(".",A1)))&"
is in "&A2

Regards

Trevor

"Slashman" wrote in message
ps.com...
Hi Trevor,

I get for your first solution #VALUE? and the following for the second
solution:-

This weight (square symbol) 123 (square symbol) is in g

Any ideas? Thanks so far.

Cheers,

Aaron.


Trevor Shuttleworth wrote:
Actually

="This weight
"&INT(A1)&IF(ISERROR(FIND(".",A1)),"","."&MID(A1,F IND(".",A1)+1,LEN(A1)-FIND(".",A1)))&"
is in "&A2


"Slashman" wrote in message
ups.com...
Hi,

The problem with both of these solutions is, I have to manually edit
the "0.00" conditon in brackets to match the DP of the keycell. I want
this to be altered automatically via a keycell with a macro, but
obviously this is harder with TEXT formatting in the same formula.

Any other way to achieve this?

Cheers,

Aaron.


Teethless mama wrote:
Try this:

="This weight "&TEXT(A1,"0.00")&" is in "&A2


"Slashman" wrote:

Hi,

I have a cell that has a mixture of text and numbers. The text comes
through fine, but the numbers only come through as numbers whole ie
300
not 300.00. EG:


="This weight "&A1&" is in "&A2


A1 is 300.00
A2 is g
The cell displays:
This weight 300 is in g
But I would like it to display:
This weight 300.00 is in g

This cant be done with just TEXT formatting, because the numbers DP
I
want isnt always going to be 30.00 sometimes it could be 30.0000 and
I
would like to be able to have that automatically applied through a
macro, but can it be done by mixing text and number formatting?

Thanks in advance for any help,

Aaron.