Formula text and decimal place

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

 

[Bob Phillips]

I am not clear as to why Trevor's formula is so complex, as this seems to do
it

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

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

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.

 

[Aaron]

Hi Bob,

Your suggestion does not cater for preservation of the Decimal Placings
of the original number in the final statement. Can you elaborate any
more please?

Cheers,

Aaron.

 

[Bob Phillips]

it preserves it for me.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Hi Bob,

Your suggestion does not cater for preservation of the Decimal Placings
of the original number in the final statement. Can you elaborate any
more please?

Cheers,

Aaron.

 

[Aaron]

Hi Bob,

Well it doesent for me. What should I check to get the same results you
do?

I dont understand how a simple tying of cells together with &A1& etc
can preserve the decimal places in A1. This is why Trevor wrote the
formula he did.

Cheers,

Aaron.