Int Iserror Len Mid Find formula

[Aaron]

Hi,

Trevor Shuttleworth kindly wrote this formula for me:

A1=300
A2=g

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,FIND(".",A1)+1,LEN(A1)-FIN­D(".",A1)))&"
is in "&A2

But, I have 2 areas I would like to refine it.

1> If I use a negative number in A1, I get 1 added to the number like
this: -0.5 becomes -1.5 and I dont want that

2> I need this to work when using zeroes as trailing numbers, not
always fractional decimal places: 300.00 instead of 300.12 currently it
sees 300.00 as just 300.

Can anyone help with this?

Thanks in advance,

Aaron.

 

[Biff]

Maybe this:

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

Biff

Hi,

Trevor Shuttleworth kindly wrote this formula for me:

A1=300
A2=g

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,FIND(".",A1)+1,LEN(A1)-FIN­D(".",A1)))&"
is in "&A2

But, I have 2 areas I would like to refine it.

1> If I use a negative number in A1, I get 1 added to the number like
this: -0.5 becomes -1.5 and I dont want that

2> I need this to work when using zeroes as trailing numbers, not
always fractional decimal places: 300.00 instead of 300.12 currently it
sees 300.00 as just 300.

Can anyone help with this?

Thanks in advance,

Aaron.