We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2012-03-08 15:41:04 +0200

updated 2021-06-04 01:22:54 +0200

Alex Kemp gravatar image

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-15 14:59:19.817537

2 Answers

Sort by » oldest newest most voted

answered 2012-03-08 18:46:23 +0200

Pedro gravatar image

updated 2012-03-08 18:47:19 +0200

Of course there is ;)


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.

edit flag offensive delete link more


Confirmed. This works very well!

qubit gravatar imagequbit ( 2013-02-16 02:34:53 +0200 )edit

answered 2012-03-10 16:56:38 +0200

L-user gravatar image

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: https://bugs.freedesktop.org/show_bug.cgi?id=47185

edit flag offensive delete link more

Question Tools


Asked: 2012-03-08 15:41:04 +0200

Seen: 901 times

Last updated: Mar 10 '12