Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .