Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

When formatting a cell in Excel (or LibreOffice), how can I have a certain symbol displayed in a specified position?

For instance, I want a cell formatted so that the sixth symbol is a hyphen, eg. "2121D-34".

share|improve this question
    
You need to explain your problem in more detail. –  steampowered Aug 23 '12 at 20:19

2 Answers 2

It looks like you can't do what you're you're asking. If your data was purely numerical, a Custom Cell Format of "#####-##" would work fine (displaying "1234567" as "12345-67"). However, as soon as you involve a non-numeric character (e.g. the "D" in your example), this no longer works. One source I found states that, when it comes to formatting cells in Excel, "there is not an awful lot more you can do with text".

I haven't used LibreOffice, but their help wiki shows that your results there will likely be similar, with plenty of numerical formats but few (if any) text formats available.

share|improve this answer
    
Thank you for the help! –  Angelo Rigo Aug 27 '12 at 19:56

From the Excel 'Format Cells' dialog:

"Text format cells are treated as text even when a number is in the cell. The cell is displayed exactly as entered."

A work-around is to use a helper column that some text formulas. Using your example,

=LEFT(E1,5)&"-"&RIGHT(E1,2)

will return your data formatted the way you want. If your base data varies, then you'll have to get a little more sophisticated in your formula.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.