Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

too many decimals places returned with concat from field with only two decimals

Copper Contributor

Hello! I'm learning more complicated excel through here and really appreciate all of your expert help. I haven't been able to find the answer to this... I've tried fixed and trunk, and they return with errors.

 

I"m using this:

=CONCAT([@[Approx Dimentions]]&CHAR(10)&"Appox Wt (oz): ",[@[kg to oz]]&CHAR(10)&" "&CHAR(10)&[@description]&CHAR(10)&" "&CHAR(10)&[@Origin])

 

and it returns this (this one doesn't have an origin)

Approx Dims (mm): 42 x 42 x 42
Appox Wt (oz): 3.739039972

 

I want the Wt to be 3.74 as it is in the "kg to oz" column that is set to number with two decimal places, and is also a result of a formula =Q6*35.273962. I have tried to change this to 35.27 and that doesn't do anything.

Luwana1_0-1707498963848.png

 

thank you in advance for your help and understanding that I'm new

 

2 Replies
best response confirmed by Luwana1 (Copper Contributor)
Solution

@Luwana1 

Use the TEXT function:

 

=CONCAT([@[Approx Dimentions]],CHAR(10),"Approx Wt (oz): ",TEXT([@[kg to oz]], "0.00"),CHAR(10),CHAR(10),[@description],CHAR(10),CHAR(10),[@Origin])

 

or

 

=[@[Approx Dimentions]]&CHAR(10)&"Approx Wt (oz): "&TEXT([@[kg to oz]], "0.00")&CHAR(10)&CHAR(10)&[@description]&CHAR(10)&CHAR(10)&[@Origin]

Thank you! The first one worked perfectly! thank you so much!
1 best response

Accepted Solutions
best response confirmed by Luwana1 (Copper Contributor)
Solution

@Luwana1 

Use the TEXT function:

 

=CONCAT([@[Approx Dimentions]],CHAR(10),"Approx Wt (oz): ",TEXT([@[kg to oz]], "0.00"),CHAR(10),CHAR(10),[@description],CHAR(10),CHAR(10),[@Origin])

 

or

 

=[@[Approx Dimentions]]&CHAR(10)&"Approx Wt (oz): "&TEXT([@[kg to oz]], "0.00")&CHAR(10)&CHAR(10)&[@description]&CHAR(10)&CHAR(10)&[@Origin]

View solution in original post