Check out some similar questions!

joed123 · Apr 10, 2008, 01:55 AM

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 i.e.. Change h67g02 to h067g02 without changing y124f07 and without messing up the last two numbers (07 or 02). Please help. Thanks Joe

KISS · Apr 10, 2008, 04:53 AM

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.

KISS · Apr 10, 2008, 06:43 AM

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 length is not equal to 6.

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