Custom Format Question

How Do I code in a custom format so Excel recognizes to insert dashes into specifc sequences of Letters and numbers.

For example:

If I enter 1ap33a1

I want it to automatically change it to:

1-AP-33-A-1

Is this possible without using VBA? Thank you.

You can't apply a number format to the example you gave, because it is not a number, however, you could apply a formula, which will appear in the next cell for example:

=LEFT(A1,1)&"-"&MID(A1,2,2)&"-"&MID(A1,4,2)&"-"&MID(A1,6,1)&"-"&RIGHT(A1,1)

This assumes the entry has been placed in cell A1.

The other alternative is to apply a data validation, to force the user to enter the data in the format you need. If it is entered incorrectly you can force the user to correct it.

How would i do that

select Data | Validation

On the settings tab select custom from the allow dropdown box, then in the formual bar (assuming you are in A1) enter the following:-

=AND(MID(A1,2,1)="-",MID(A1,5,1)="-",MID(A1,8,1)="-",MID(A1,10,1)="-")

Adjust a1, if it is in an alternative cell.

If you will always have numbers in postion 1, 6/7 and 11, then you can add the following:

isnumber(value(MID(A1,1,1))),isnumber(value(MID(A1,6,2))),isnumber(value(MID(A1,11,1))) at the end of the above formula (before the close bracket)

If you need to check for letters in positions 3/4 and 9, you could also add something for that.

Regards

Gary