0
  A        B            C   D

2 DRUGS 000000000004    2   PARACETAMOL (ACETAMINOPHEN)  TAB 500 MG

This is my entry in my open office so we have here the row 2 with columns A-D I have create a formula =CONCATENATE("('" ;A2;"','";B2;"','";C2;"','";D2;"'),")

and this one give me this result:

('DRUGS','4','2','PARACETAMOL (ACETAMINOPHEN) TAB 500 MG'),

Basically I want a result like:

('DRUGS','000000000004','2','PARACETAMOL (ACETAMINOPHEN) TAB 500 MG'),

Column B is set to Number with Leading zeroes set to 12.

What I want is to get a result where the leading zeroes in column B is will retained.

Pekka
  • 1,075
  • 2
  • 10
  • 17

2 Answers2

1
=CONCATENATE("('" ;A2;"','";TEXT(B2;"000000000000");"','";C2;"','";D2;"'),")

Use any mask you want as the second parameter to the TEXT function

More on TEXT and other text functions: https://help.libreoffice.org/Calc/Text_Functions#TEXT

Júlio Reis
  • 173
  • 11
0

Please try:

=CONCATENATE("('";A2;"','";REPT("0";12-LEN(B2));B2;"','";C2;"','";D2;"'),")

I suspect what you have in B2 is 4 formatted with many leading 0. If so, it is possible, assuming many other entries, that some will be text and the cell content actually something like 000000000004 - for which the above formula will not work (but yours should).

pnuts
  • 58,317
  • 11
  • 87
  • 139