How to keep leading zero and add comma in openoffice calc formula?

openoffice-calc openoffice-basic
user1367204 picture user1367204 · May 31, 2012 · Viewed 10.8k times · Source

I have 6 fields in a row in open office, the 1st is a word, the 2nd, 3rd, and 4th are a number with a leading zero, the 5th and 6th are regular numbers. How do I join them all together with a comma between them so that the leading zero stays?

Answer

Excellll picture Excellll · May 31, 2012

Based on your comment about your numbers having a leading 0 in virtue of a custom number format, you need to incorporate TEXT() functions into your formula to retain (i.e., add) your leading 0s.

=CONCATENATE(A1,",",TEXT(B1,"0#####"),",",TEXT(C1,"0#####"),",",TEXT(D1,"0#####"),",",E1,",",F1)

Just be sure to include as many #'s as the max length of a number in that field.

Related questions

Run shell command and return output as result of custom function

I would like to write a custom OpenOffice function that runs a shell command and puts the result into the cell from which it was invoked. I have a basic macro working, but I can't find a way to capture …

Read More
How to quote a quote with the CONCATENATE function in OOCalc

In OOCalc I want to use the CONCATENATE function to add quotes to each string in column A. So in cell B1 I want to do: =CONCATENATE("\"",A1,"\"") OOCalc does not like this, or without the escaping backslash. Does anyone …

Read More
How to add number of days to a date, consider only business days (i.e. ignore weekends)?

I'm trying to create a formula to calculate the n-th Business Date (only Monday to Friday are business days). For simplicity's sake, holidays are not important; only weekends should be ignored. For example: +------------------------------------------------------------------ | A B C +------------------------------------------------------------------ 1 | Starting Date …

Read More