Ask Your Question
0

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

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

updated 2012-03-08 15:50:12 +0200

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:

A9A9A9A9A9A9A9A9A9A9A9A9

I'd like to format it as:

A9A9-A9A9-A9A9-A9A9-A9A9-A9A9

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
2

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

Pedro gravatar image

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

Of course there is ;)

=LEFT(A1,4)&"-"&MID(A1,5,4)&"-"&RIGHT(A1,4)

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

Comments

Confirmed. This works very well!

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

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

Stats

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

Seen: 446 times

Last updated: Mar 10 '12