Macro for TEXT and DP
 

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.

Macro for TEXT and DP
 

Aaron

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

Regards

Trevor


"Slashman" wrote in message
oups.com...
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.

Macro for TEXT and DP
 

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.

Macro for TEXT and DP
 

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.

Macro for TEXT and DP
 

="This weight "&INT(A1)&"."&MID(A1,FIND(".",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.

Macro for TEXT and DP
 

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.

Macro for TEXT and DP
 

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.

Macro for TEXT and DP
 

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.

Macro for TEXT and DP
 

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.

Macro for TEXT and DP
 

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.

Macro for TEXT and DP
 

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.

Macro for TEXT and DP
 

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.

Slashman wrote:
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.