| 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. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| 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 | |||
Linear Mode
