Google Sheets – Concatenation Truncating Numeric Value

concatenategoogle sheets

in a cell J21 I have entered a numeric value 1.10000. The cell is set to numeric value with 5 decimal places.

When using concatenate =CONCATENATE("=CONCATENATE("@ £/€ rate: ",(J21)) in another cell elsewhere on the sheet, I get the result "@ £/€ rate: 1.1 therefore truncating the full value shown (1.10000).

Is there a way to concatenate the full value including any zeros?

Following the answer provided by @marikamitsos the following results were produced:

The issue seems clearly at the door of limitations to CONCATENATE since a straight reference to the cell containing the value gives the result required.

Best Answer

Answer updated

(as a reply to your edit, comment as well as your own accepted answer.)

I am afraid you jump to the wrong conclusions thus also misleading the readers.

Why? Because...

Just as in this hidden/missing text.
1. In your statement "in a cell J21 I have a set value 1.10000" you merely reveal what is present in the cell.
2. Further more you fail to show how this value was placed there (whether manually or as a result of a formula/script).
3. Not even how it is formatted or presented.
4. More so whether the value is a number or text.
5. Needless to mention the reference to its Horizontal alignment (in your later added image)
6. Finally, you do not take into consideration any of the aforementioned points even after your edited question or more so in your accepted answer.

The above may seem to be insignificant, still truly essential and should not be missed for the right set of conclusions.

Thus you arrive to a wrong/partial assumption

"concatenate doesn't work in ...as both solutions still truncate the numeric result."

All given solutions (yours and mine) work.

The correct conclusion

The issue does NOT lay on CONCATENATE itself.
Depending on the situation different solutions may apply.

When asking a question please share all relevant info and even better share an editable sheet so you can be easier helped.

Original answer

You should change your formula syntax to

=CONCATENATE("@ £/€ rate: ",(J2))

You could also use

="@ £/€ rate: "&J2

(Please adjust ranges to your needs)

Functions used:

CONCATENATE

Related Solutions

Google Sheets – Best Concatenation Options

CONCAT() and & do the same thing, so in that case there isn't a difference between them (just personal preference I suppose)

CONCATENATE() can also be used to join 2 items, however it's typically used to join greater than 2 terms.

Which one is best? I'd say whichever option makes the formula the most readable, in this case I would say CONCAT() only because you can easily identify which function you're running, and the terms "TEST" and B2 are clearly defined as the arguments.

Google-sheets – Time duration, when converted to decimal, shows negative

Google Sheets does recognize the date-time combination. This is what I get after entering the dates you gave in A1 and B1, and entering =B1-A1 in the cell C1:

+---+---------------------+--------------------+---------------+
|   |          A          |         B          |       C       |
+---+---------------------+--------------------+---------------+
| 1 | 10/27/2015 23:38:00 | 10/28/2015 1:38:00 | 0.08333333334 |
+---+---------------------+--------------------+---------------+

No error; the output is expected: the difference is 0.08333.. of the day, i.e., 2 hours. The column C should be formatted as Duration to make the output user friendly.

Then I tested addition; it also works as expected, with C4 being SUM(C1:C3).

+---+---------------------+--------------------+----------+
|   |          A          |         B          |    C     |
+---+---------------------+--------------------+----------+
| 1 | 10/27/2015 23:38:00 | 10/28/2015 1:38:00 | 2:00:00  |
| 2 | 10/27/2015 15:38:00 | 10/28/2015 4:38:00 | 13:00:00 |
| 3 | 10/27/2015 14:38:00 | 10/28/2015 2:38:00 | 12:00:00 |
| 4 |                     |                    | 27:00:00 |
+---+---------------------+--------------------+----------+

Conclusion: something went wrong at the stage of entering data in your sheet. Check whether 10/27/2015 is recognized (autoformatted as date) when you enter it in a cell. This behavior depends on your locale setting.