Automatically add a zero '0' to the front of a group of numbers

Question

pdhussey Author

Level 1

8 points

Automatically add a zero '0' to the front of a group of numbers

I have a page/ List of EAN 13.

Eg:

799430072267

799430072274

These numbers need to have a zero / 0 added to the front of each number sequence.

When the numbers were copied in the 0 was dropped.

I have since changed the cell formating to text, which now allows the number 0 to be added.

However I do not want to have to manually add a zero to every number as there are 1,500 of them.

Can someone tell me the funtion forumlar that can do this automatically.

Using the find and replace otpion will not work, as there are too many variances with the leading number been replicated in the number sequence.

Thank you.

Paul

iMac, Mac OS X (10.6.2)

Posted on Nov 12, 2013 9:58 PM

Reply

Answer 1

Yellowbox

Level 7

21,951 points

Nov 12, 2013 10:05 PM in response to pdhussey

Hi pd,

Solution posted by Jerry in another thread.

Format Inspector > Cell > Data Format > Numeral System.

Use Base 10 and set the number of places to include leading zeroes.

Regards,

Ian.

Reply

Answer 2

Barry

Level 9

60,035 points

Nov 13, 2013 12:14 AM in response to pdhussey

Hi Paul,

As EAN 13 'numbers' are really 'text strings' in terms of their use, I'd prefer using a text string instead of the Numeral Syatm format solution suggested by Ian.

Add a temporary column to the right of the one containing these codes, then, working in that new column, insert one of the formulas below into the first data row, and fill it down to the last data row. The formulas assume that the faulty EAN 13 codes are in column A, and the new column is column B.

If all of your EAN 13 values have 12 digits, and are missing only a singel leading 0, use this formula:

=RIGHT("0"&A,13)

This will append a single zero to the beginning of each value in column A, then strip it off IF it's addition results in a 14 digit code.

If any of your faulty codes require more than a single zero, use this version:

=RIGHT("0000000000000"&A,13)

This concatenates a strng of 13 zeros and the contents of the cell in column A., then returns the 'last' 13 characters of that string. Rows without an entry in column A will return "0000000000000".

If you would prefer 'empty' rows to remain 'empty', make this final edit:

=IF(LEN(A)>0,RIGHT("0000000000000"&A,13),"")

In whichever case you choose, follows thee steps:

Enter the chosen formula in the first body row of column B (assumed), then Fill down.

Seelct all of these cells, and Copy.

Select the first body row cell of Column A.

Go Edit > Paste Values.

Regards,

Barry

Reply