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

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

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.

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

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

# Re: too many decimals places returned with concat from field with only two decimals

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]

# Re: too many decimals places returned with concat from field with only two decimals

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

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

# Re: too many decimals places returned with concat from field with only two decimals

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]