How to add a leading space with a format string?

[kalliklles]

I am converting centimeters to American Feet/Inches in a single column and I would like to obtain a properly formatted and correctly aligned text. E.g.

480    16'   7"
551    18'  11" 

I am doing the cm/ft-in conversion in a formula and then converting and concatenating to text, with something like the following:

=TEXT(ROUND(G12/30.48),"##")&"' "&TEXT(ROUND(MOD(G12,30.84)/2.54),"##")&CHAR(34)

However, I lose the alignment in the inch part. To get the “inches” part properly aligned, I would need to insert a leading space when I convert the result to text, but I cannot find out what the the proper formatting string would be. I know I can insert leading zeros’ with “00” but I cannot find the equivalent for spaces. Perhaps such a string does not exist?

Thanks for the help.

P.S. I can roll out my own IF function, of course, but the formula would become even uglier than it is already.

[Lupp]

The second occurrence of the foot in your formula is wrong (twisted).

[Lupp]

You need to add your space(s) as a text constant in front of the “central” formatting code consisting of characters with special meaning. Doing this in the code line of the Format-Cells dialogue you use the doublequotes around the literals leaving the controlling characters outside. These doublequotes must also be created for a format code passed to the TEXT function. The formula
=TEXT(PI();CHAR(34)&"This is pi with 5 decimal places: "&CHAR(34)&"0.00000") will return the text
This is pi with 5 decimal places: 3.14159
in a locale using the decimal point. To get it for a global scope use
=TEXT(PI();CHAR(34)&"This is pi with 5 decimal places: "&CHAR(34)&"0"&MID(1/2;2;1)&"00000")

To more detail:
The proper way to align digits according to their value in place is to use leading zeros. Even if you decide to inflate your expression with a conditional space inserted, you won’t get a precise alignment except if you use a monotype font: Spaces are narrower than digits! Only digits are “monospace” in all fonts.

See this attached demo.
(The attachment was updated once. Original version contained errors.

Down with all this foot and inch shit! There was some progress since 1880.

[librebel]

Hello @kalliklles,

you could make this alignment by inserting an IF() in your formula:

=TEXT(ROUND(G12/30.48),"##")&"' "&TEXT(ROUND(MOD(G12,30.84)/2.54),IF(ROUND(MOD(G12,30.84)/2.54)>9,"","?")&"##")&CHAR(34)

Then set the Font of this column to a Monospace font such as “DejaVu Sans Mono”.

EDIT:
or try this formula ( without IF part ) to align towards the right of the cell:

=TEXT(ROUND(G12/30.48),"?0")&"' "&TEXT(ROUND(MOD(G12,30.84)/2.54),"_* ?0")&CHAR(34)