How can I format a single cell to include spaces or hyphens?

I am entering serial numbers in a Calc spreadsheet. I’d like to be able to enter the raw data while making it easy to read it in the intended format as presented on the boxes.

Here’s an example of what I enter:


I’d like to format it as:


Using “@” in cell formatting only seems to allow using text of a cell for formatting, not reformat the same cell.

Short of breaking my serial #s in different cells and putting them together again in a new cell =CONCATENATE(C6,"-",D6,"-",E6,"-",F6,"-",G6), is there a shorter/more effective way of doing this?

Of course there is :wink:


You need to repeat the MID part as many times as the number of groups in the middle.

I prefer to use & instead of CONCATENATE(). It’s easier to read.

Confirmed. This works very well!

I have very similar problem and it looks like using Format | Cells there is no way of solving this problem, there is just # for numbers like: ##-##-## to format AB-CD-ED, but nothing similar for text. I have reported enhancement request on bug-tracker: