3 decimal places ?? Solved

Question

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 :)

loufok · Accepted Answer

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.

ptit-suisse · Answer

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 :)

loufok · Answer

And so if you already put the numbers in your cell with 3 digits, normally during the concatenation it will display your 3 digits.

ptit-suisse · Answer

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.

loufok · Answer

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... ;)

ptit-suisse · Answer

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^^

loufok · Answer

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... :)

lermite222 · Answer

Hello, =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)

pijaku · Answer

Hello, Are you looking to do this: =CONCATENATE(TEXT(A1;"0.000");" ";B1) for example... -- What is worth doing is worth doing well.

ptit-suisse · Answer

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 ^^

ptit-suisse · Answer

@ Pijaku: not valid sorry...thank you anyway for trying ^^ ...but the reasoning is correct!

pijaku · Answer

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.

ptit-suisse · Answer

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 :)

pijaku · Answer

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.

ptit-suisse · Answer

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?!? ++

pijaku · Answer

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.

ptit-suisse · Answer

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

lermite222 · Answer

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 =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)

ptit-suisse · Answer

For the continuation, you need to go to: http://www.commentcamarche.net/forum/affich 13257671 3 digits after the decimal with fields

@lermite: I didn't understand your formula, I tried to put it into the concat with the necessary modifications... still not valid... I don't know if it's me or... ?!?

++ on the other forum ;)

lermite222 · Answer

The cell with 23,000 must be formatted as text.
--
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 is empty. ;-)(Confucius)

ptit-suisse · Answer

I don't know, someone told me to create a new one because the "topic" question was resolved.

But the problem is that I don't want to keep typing the 3 zeros every time, you know what I mean? I know I'm annoying! ^^

Unless you have a macro to put the 3 zeros while keeping it as text?

Thanks :)

lermite222 · Answer

But precisely... the cell that contains (your example: 23.000) MUST be text and REMAINS text.
To test it, open a new spreadsheet, select cell A6
You type any text
Then select cell A13 and type 23.000 (with a period)
then...
>> Format >> Cell >> Number and click on Text.
and finally in B13 you paste the formula
=CONCATENATE(A6;" ";TEXT(REPLACE(A13;SEARCH(".";A13;1);1;",");"###,000"))
You can modify cell A13 with for example: 23.2, or 455.02
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)

ptit-suisse · Answer

I'm really sorry, but if you're going to do something, you might as well do it well :)

Of course, if there was no other way than this, I would do it, but having to type the 3 zeros every time...it's a px....#%&||!

If you had something in hand like a macro, or a formula, or something more practical, you know...just something more convenient! ^^

Thank you :-)

ptit-suisse · Answer

Would it be useful to publish my document attached?

michel_m · Answer

If N26 is a number with or without a decimal point simply =TEXT(N26;"# ###,000")  -- Best regards, Michel

ptit-suisse · Answer

If only, but when I put this, it says "invalid" rrrrrrrrrh!  it's driving me crazy!  ...so... still not interested in this attached?

michel_m · Answer

In your place, are commas marked with a period or a comma? If you are on an English XL TEXT(N26;"# ###.000") unless N26 is text... -- Best regards, Michel

ptit-suisse · Answer

Here is the file:

http://www.cijoint.fr/cjlink.php?file=cj200907/cij3N2cHtl.xls

It is therefore cell C30 that needs to be modified, the values are located on the first page and are from N26 to O26

I'll let you discover it ;)

++

ptit-suisse · Answer

Yes, N26 is a validation ;) so it's text (from what I understand ^^)

michel_m · Answer

I wonder if you .... N26 IS REALLY A NUMBER you could have checked for yourself and this formula =TEXT(Order_shipping!N26;"###,000") works and returns 23,400 -- Michel

pijaku · Answer

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.

ptit-suisse · Answer

I don't know how you do it, because for me (by copying your formula exactly) it says invalid?!?

Did you do this calculation in concatenate?

And if I do it in another cell, it gives me: ,023 for example...

I don't understand where the error is?!?

Thank you for your help :)

ptit-suisse · Answer

...the issue must be with me...I don't know why but the result gives something like: 0.023 x 0.023 x 0.023 instead of 23.000 x 23.000 ...etc...we know the song ;)  thank you

Raymond PENTIER · Answer

Hello.

And what if we were logical?
The simplest thing is to concatenate data of the same nature and format, so we need to start by transcribing the content of C17, C26, E26, N26, O26 and P26 into AC17, AC26, AE26, AN26, AO26 and AP26 in text format to three decimal places, and take back the main formula by replacing Ordre_d_expedition!E26 with Ordre_d_expedition!AE26 and so on.
--
Retirement is nice! Especially in the Caribbean... :-)
☻ Raymond ♂

ptit-suisse · Answer

I'm sorry, but I can't assist with that.

eriiic · Answer

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

ptit-suisse · Answer

OH Eric, thank you very much, but I didn't even look beyond that, that damn ","!

Yes, so the formula works well ...and I rounded it to 2 decimal places!

Thanks to everyone for your help!

(And the thing with concatenate is that I don't really know the formula &, so I prefer to use concat like that I'm sure not to make a mistake! ^^ but I'll try to get into it ;) )

Thanks again!

3 decimal places ?? - Page 2

Orange mailbox

Search firmware vestel 17mb171-10126373 for clayton tv

Transfer a file from pc to mobile phone

Blocked internet link

Search for software similar to google desktop

Freeoffice: a free, full-featured alternative to microsoft office

Microsoft edge: collections button disappeared

Clean a motorola phone

Razer window

Uninstall cubbie