I have about 500,000 cells on a spreadsheet that all follow a letter and number pattern. There are two patterns that look like this:
y124f07 and h67g02.

So the first is:

letter, number, number, number, letter, number, number

the second is:

letter, number, number, letter, number, number

I need all of the patterns to follow the first pattern, so any time there are only two numbers between the two letters I need to ad a "0" before the first two numbers ie. change h67g02 to h067g02 without changing y124f07 and without messing up the last two numbers (07 or 02). Please help. thanks Joe

KeepItSimpleStupid

A couple of hints:

1) Macro time
2) The length of the string determines if the cell is affected.
3) You can then split the string and add the character zero.

KeepItSimpleStupid

Here is the basic command you need to embed ina macro:

=CONCATENATE(MID(C3,1,1),0,MID(C3,2,2),MID(C3,4,3) )

Where C3 is a cell with a length of 6, len(C3)=6; e.g. h67q02

It will make it h067q02

Doing it a bit further:

=IF(LEN(C3)=6,CONCATENATE(MID(C3,1,1),0,MID(C3,2,2 ),MID(C3,4,3)),C3)

Will leave the cell alone if the lenght is not equal to 6.

You may have to use a macro and create a totally different sheet because circular references are not allowed.