How to retain leading zero's and dashes.

Question

Level 1

4 points

How to retain leading zero's and dashes.

Hi all, I am trying to write a column of SKU's. The first lot will start with "A20" because that is the name of the stock (Angels 20, a tabletop dogfighting game 😉)

I am trying to follow it with -001, -002 etc. I want to be able to enter the first one, A20-001, and drag down filling out the cells.

If I format Cells as Text, it deletes the leading zero's. If I remove the dash, it retains the Zero's, because it thinks I am writing twenty thousand something, not zero. Using an apostrophe does not work, and it would annoy me to look at anyway.

Formating as Numeral System does not work either.

I am on Numbers 09.

Thanks all!

MacBook Pro with Retina display, OS X Yosemite (10.10.2), 2.2 GHz Intel Core i7

Posted on Nov 23, 2015 12:44 AM

Reply

Answer 1

Best reply

Yellowbox

Level 7

20,383 points

Nov 23, 2015 1:48 AM in response to Xen Ochren

Hi Xen,

Column B is formatted as Custom

Hide Separator (the thousands comma)

Show Zeroes for Unused Digits

Number of Digits 3

Type 001 then 002.

Select both cells and Fill Down to get the series.

Formula in C2 (and Fill Down)

="A20-"&B2

(the & will concatenate A20- and the number from B)

Regards,

Ian.

Reply

Answer 2

Hiroto

Level 5

7,461 points

Nov 23, 2015 3:42 PM in response to Xen Ochren

Hello

You may define custom format as A20-000. This way, when you input 1 in B2 and 2 in B3, you'll see A20-001 and A20-002 respectively and now you can select B2:B3 and fill down to generate the sequence of SKU's.

Notes.

Column B is formatted by the custum format A20-000. Actually it contains numbers.

Column C converts the numbers in B to text, if necessary.

Table is built with Numbers v2.0.5.

Hope this may help,

H

PS. Table dump is as follows.

Table 1 (excerpt) A1 automatic A2 A20-001 A3 A20-2 A4 A20-3 A5 A20-4 A6 A20-5 A7 A20-6 A8 A20-7 A9 A20-8 B1 custom format A20-000 B2 1 B3 2 B4 3 B5 4 B6 5 B7 6 B8 7 B9 8 C1 =""&B C2 =""&B2 C3 =""&B3 C4 =""&B4 C5 =""&B5 C6 =""&B6 C7 =""&B7 C8 =""&B8 C9 =""&B9

EDIT: added PS.

Reply