3 decimal places ??
Solved
ptit-suisse
Posted messages
256
Status
Member
-
ptit-suisse Posted messages 256 Status Member -
ptit-suisse Posted messages 256 Status Member -
Hello,
I wanted to know if there is a formula for a CONCATENATE field to have 3 digits after the decimal point? (I tried with TEXT, but I fear my knowledge may not be advanced enough....(TEXT;"####,###")
Thank you for your help :)
I wanted to know if there is a formula for a CONCATENATE field to have 3 digits after the decimal point? (I tried with TEXT, but I fear my knowledge may not be advanced enough....(TEXT;"####,###")
Thank you for your help :)
Configuration: Windows Vista
37 answers
- 1
- 2
Next
If I understand correctly, you want a result with 3 digits after the decimal point? You need to right-click in the cell - number, and there you choose the display style you want, including the number of decimals.
No, actually, that goes beyond my knowledge... This post is, in my opinion, resolved because the initial question:
I wanted to know if there is a formula for concatenating fields to have 3 digits after the decimal point?
is resolved by:
(TEXT(A1,"0.000")
You should therefore mark this post as "resolved" and open a new one, clearly stating that your workbook refers to "fields" or others...
Best regards
--
What is worth doing is worth doing well.
I wanted to know if there is a formula for concatenating fields to have 3 digits after the decimal point?
is resolved by:
(TEXT(A1,"0.000")
You should therefore mark this post as "resolved" and open a new one, clearly stating that your workbook refers to "fields" or others...
Best regards
--
What is worth doing is worth doing well.
Well, there you go... Everything comes to those who wait...
Your formula works, it was just a matter of knowing where to place the "TEXT(xxx;"0.000")" like this:
=CONCATENATE(Order_Shipping!E26&" "&"PALETTE"&IF(OR(Order_Shipping!E26="1";Order_Shipping!E26="1/2");"";IF(Order_Shipping!C17="FR";"S";"N"));" ";CONCATENATE("NR. ";Order_Shipping!C26);" ";CONCATENATE(TEXT(Order_Shipping!N26;"0.000");" x ";TEXT(Order_Shipping!O26;"0.000");" x ";TEXT(Order_Shipping!P26;"0.000");" cm"))
I tried it and it works quite well...
However, do not replace 0.000 with ###,000 because for me, it doesn't work!!
A+
--
What is worth doing is worth doing well.
Your formula works, it was just a matter of knowing where to place the "TEXT(xxx;"0.000")" like this:
=CONCATENATE(Order_Shipping!E26&" "&"PALETTE"&IF(OR(Order_Shipping!E26="1";Order_Shipping!E26="1/2");"";IF(Order_Shipping!C17="FR";"S";"N"));" ";CONCATENATE("NR. ";Order_Shipping!C26);" ";CONCATENATE(TEXT(Order_Shipping!N26;"0.000");" x ";TEXT(Order_Shipping!O26;"0.000");" x ";TEXT(Order_Shipping!P26;"0.000");" cm"))
I tried it and it works quite well...
However, do not replace 0.000 with ###,000 because for me, it doesn't work!!
A+
--
What is worth doing is worth doing well.
Hello everyone,
The pijaku formula works.
And since he told you that he tested it, you could have looked into it a bit more...
However, his decimal separator is ',' and yours is '.'.
Now, is it really worth the hassle to indicate a pallet dimension to the hundredth of a millimeter?
On the other hand, putting CONCATENATE() inside another CONCATENATE() is pointless, as it just makes the formula longer.
You can include 30 strings in a CONCATENATE().
And using & instead of CONCATENATE() improves readability: A1 & A2 & A31...
eric
The pijaku formula works.
And since he told you that he tested it, you could have looked into it a bit more...
However, his decimal separator is ',' and yours is '.'.
Now, is it really worth the hassle to indicate a pallet dimension to the hundredth of a millimeter?
On the other hand, putting CONCATENATE() inside another CONCATENATE() is pointless, as it just makes the formula longer.
You can include 30 strings in a CONCATENATE().
And using & instead of CONCATENATE() improves readability: A1 & A2 & A31...
eric
The issue is that the cell also has text (the reason for CONCATENATING)
at the same time I specify that the values do not always have a digit after the decimal point, but even if that's the case, I still need 3 digits after the decimal point...
Thank you for your help :)
at the same time I specify that the values do not always have a digit after the decimal point, but even if that's the case, I still need 3 digits after the decimal point...
Thank you for your help :)
And so if you already put the numbers in your cell with 3 digits, normally during the concatenation it will display your 3 digits.
But actually, it’s a format, in the cell that is reported in CONCATENATE, (the 0s after the decimal are a format (number, 3 0s after the decimal)) but in CONCATENATE, the format doesn’t come through...
Thanks again.
Thanks again.
Uh.. I didn't quite catch that
format of what? From what you told me, you're concatenating a text column with a number column, right? So I'm telling you to initially change the number cell format of your number column and that will reflect in your concat' column
whatever you modify in your cells that are later concatenated will be reflected in your concatenated result
or maybe we’re not understanding each other, and in that case, I have no idea what you want... ;)
format of what? From what you told me, you're concatenating a text column with a number column, right? So I'm telling you to initially change the number cell format of your number column and that will reflect in your concat' column
whatever you modify in your cells that are later concatenated will be reflected in your concatenated result
or maybe we’re not understanding each other, and in that case, I have no idea what you want... ;)
Well, I'm making a form where some data is then replicated across several pages
For clarity, I will name the cell in which CONCATENATE should look for the formula A1. A1 is formatted to have 3 decimal places and is an unlocked cell (it's a validation)
Then, the cell containing the concatenate will be C1, which also contains formulas (IF, &, and OR) as well as text... In the CONCATENATE, when I select cell A1, the format does not appear in the concate'
Got it? ;)
Thanks^^
For clarity, I will name the cell in which CONCATENATE should look for the formula A1. A1 is formatted to have 3 decimal places and is an unlocked cell (it's a validation)
Then, the cell containing the concatenate will be C1, which also contains formulas (IF, &, and OR) as well as text... In the CONCATENATE, when I select cell A1, the format does not appear in the concate'
Got it? ;)
Thanks^^
Yes, yes, I understand! But somehow... I don't know (I just tested it on my Excel and it works)
I leave the space, powerless in front of your problem which I am sure is easy to solve.
Hang in there... :)
I leave the space, powerless in front of your problem which I am sure is easy to solve.
Hang in there... :)
Hello,
See you +
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
=CONCATENATE(A6;" ";TEXT(A13;"###,000"))
See you +
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;-)(Confucius)
Hello,
Are you looking to do this:
=CONCATENATE(TEXT(A1;"0.000");" ";B1) for example...
--
What is worth doing is worth doing well.
Are you looking to do this:
=CONCATENATE(TEXT(A1;"0.000");" ";B1) for example...
--
What is worth doing is worth doing well.
There's a little issue with your formula, it's that instead of the result in A1 being: 23.000, in C1 it puts: ,023?!?
Thanks again!
And the thing with the pot is pretty cool ;) LOL ^^
Thanks again!
And the thing with the pot is pretty cool ;) LOL ^^
You're certainly trying a "complex" formula by nesting IF, OR, and CONCATENATE. Give us your "attempt" at the formula and we'll try it with you.
--
What is worth doing is worth doing well.
--
What is worth doing is worth doing well.
Here is my "attempt" at the formula! :
=CONCATENATE(Shipping_Order!E26&" " &"PALETTE"&IF(OR(Shipping_Order!E26="1",Shipping_Order!E26="1/2"),"",IF(Shipping_Order!C17="FR","S","N"))," ",CONCATENATE("NR. ",Shipping_Order!C26)," ",CONCATENATE(Shipping_Order!N26," x ",Shipping_Order!O26," x ",Shipping_Order!P26," cm"))
So, the cells Shipping_Order!N26, Shipping_Order!O26, and Shipping_Order!P26 should have a format with 3 decimal places after the formula
Thank you for your help :)
=CONCATENATE(Shipping_Order!E26&" " &"PALETTE"&IF(OR(Shipping_Order!E26="1",Shipping_Order!E26="1/2"),"",IF(Shipping_Order!C17="FR","S","N"))," ",CONCATENATE("NR. ",Shipping_Order!C26)," ",CONCATENATE(Shipping_Order!N26," x ",Shipping_Order!O26," x ",Shipping_Order!P26," cm"))
So, the cells Shipping_Order!N26, Shipping_Order!O26, and Shipping_Order!P26 should have a format with 3 decimal places after the formula
Thank you for your help :)
What exactly is the error message? Invalid external reference?
I tested your formula by changing your cells "Ordre_d_expedition!N26, Ordre_d_expedition!O26, and Ordre_d_expedition!P26" etc. to A1, B1 etc. on my end and it works well...
But I believe I remember your workbook; could there be complicating cells involving dropdown lists or results?
--
What is worth doing is worth doing well.
I tested your formula by changing your cells "Ordre_d_expedition!N26, Ordre_d_expedition!O26, and Ordre_d_expedition!P26" etc. to A1, B1 etc. on my end and it works well...
But I believe I remember your workbook; could there be complicating cells involving dropdown lists or results?
--
What is worth doing is worth doing well.
Just right, buddy ;)
Yes, as I said earlier, my binder is made with fields and they are all validations... there you go...
Do you want another attachment?!?
++
Yes, as I said earlier, my binder is made with fields and they are all validations... there you go...
Do you want another attachment?!?
++
Rhon....that's sad :'( well then goodbye forum!
ok then I'm making another forum always about office work ;)
maybe see you later
thanks again
ok then I'm making another forum always about office work ;)
maybe see you later
thanks again
23,000 is not a number, it's text, and we don't format text.
23000.. that's a number
or 23.023 too.
If your cells look like that, you'll need to modify them first
try with
and if the point is not always in the same place..
You say...
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;)(Confucius)
23000.. that's a number
or 23.023 too.
If your cells look like that, you'll need to modify them first
try with
=CONCATENATE(A6;" ";TEXT(REPLACE(A16;3;1;","),"###,000"))
and if the point is not always in the same place..
=CONCATENATE(A6;" ";TEXT(REPLACE(A16;SEARCH("." ;A16;1);1;","),"###,000")) You say...
--
Experience teaches more surely than advice. (André Gide)
If you bump into a pot and it sounds hollow, it's not necessarily the pot that's empty. ;)(Confucius)
- 1
- 2
Next