| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| error when running cut & paste macro | Excel Worksheet Functions | |||
| Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
| Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| Highlight Range - wrong macro, please edit. | Excel Worksheet Functions | |||
Hybrid Mode
