Custom number formatting, padding zeros in a two part number in the same cell

[pnoligistonol]

Hi

I wonder if someone could help me with some custom number formatting in excel 2007 running in windows 7.

I am working with a part number referencing system with the following format, 4digit/6digit i.e. 0123/012345

I have a lot of old data in excel that refers to the "same" part number (in the minds of those who have entered it) in different ways as preceding zeros have not been consistently used. I need to be able to standardize this data by padding the zeros in order to run lookups against it.

For example 0014/002469 might have been entered as 14/2469, 014/02469, 14/02469 etc.

The question I am asking for help with is twofold:

Firstly is there a way custom number formatting can add preceding zeros both sides of the / separator so future data however entered in correctly formatted cells will default to the 4digit/6digit format? I have already considered using a two cell entry approach for this with the entry cells formatted to 4 and 6 digits and then combined with the / separator in a third cell but I think this would create more problems than it solves unfortunately.

Secondly if I apply the formatting to existing data will this be able to update the formatting to the 4digit/6digit format or would I need to take another approach for this?

Any help that you can provide will be much appreciated

Thanks
Pnoligistonol

 

[njimack]

Welcome to the board.

Using a custom number format would only change the appearance of the cell - the content would remain unchanged. I would suggest a formula in a separate column, which can then be pasted over the existing data. The below formula should suffice, based on the example you gave.
=TEXT(LEFT(A1,FIND("/",A1)-1),"0000")&"/"&TEXT(RIGHT(A1,LEN(A1)-FIND("/",A1)),"0000")

 

[pnoligistonol]

Hi Neil

Thanks very much for that it does the job perfectly, i'd been pulling my hair out all morning.

Much obliged

Pnoligistonol