Macro for TEXT and DP

[Slashman]

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.

 

[Trevor Shuttleworth]

Aaron

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

Regards

Trevor

 

[Guest]

Try this:

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

 

[Slashman]

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.

 

[Trevor Shuttleworth]

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

 

[Trevor Shuttleworth]

Actually

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

 

[Slashman]

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]

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)-FIND(".",A1)))&"
is in "&A2

Regards

Trevor

 

[Slashman]

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.

 

[Gord Dibben]

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

 

[Slashman]

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.

 

[Slashman]

I just found the problem.

In my original post and subsequent testing I have been testing with
trailing zeroes. I just tried with fractional numbers and it works
fine. I need it to work with zeroes though.

IE 300.00 not 300.12

Cheers,

Aaron.