Forum Discussion
Cells don't merge properly (Function COMCAT / tekst.samenvoegen)
A product code is a combination of Alphabetic and Number characters
For example GB11104005P
Each character has a meaning and the total of this code needs to be 11 characters.
I have adjusted the cell format to 00 for cell 1 and 000 for cell 2
Cell 1 (A1) value shows GB
Cell 2 (B1) value shows 1
Cell 3 (C1) value shows 11
Cell 4 (D1) value shows 04
Cell 5 (E1) value shows 005
Cell 6 (F1) value shows P
I like to merge Cell 1 TO 6
When I use the function =CONCAT(A1,B1,C1,D1,E1,F1) the result is GB11145P
I tried to change the format of Cell that holds the function to 00 and 000 but this didn't change the result.
Is there another way?
2 Replies
Excel concatenates the stored values of the cells, not the values as displayed in the cells.
D1 contains the number 4, but it is formatted as "00" to display 04.
E1 contains the number 5, but it is formatted as "000" to display 005.
To concatenate formatted values, you can use the TEXT function:
=CONCAT(A1, B1, C1, TEXT(D1, "00"), TEXT(E1, "000"), F1)
=TEKST.SAMENV(A1; B1; C1; TEKST(D1; "00"); TEKST(E1; "000"); F1)
- Thank you! saved me a day! / my day 🙂
