excel alphanumeric custom format

[chipy]

i'm trying to format certain cells so that when a user inputs alphabet characters/numbers (and possibly symbols), then Excel will format it a certain way.

for example: if i type 12345678, i want excel to format it 12-345-678.
if i type ab123456, i want excel to format is ab-123-456.

how do i do this?

chipy

 

[mayest]

The number part is easy, but I don't think you can split the text that way without a macro. You could use this custom number format: 00-000-000;0;0;@

That will gve you:

12345678 -> 12-345-678
ab123456 -> ab123456 (unchanged)

The problem is that there isn't much you can do with text strings, which is what ab123456 is. You could write a macro to go through the list looking for text and then reenter it as you want it.

You could use this formula to find and reformat all of the text entries (assuming they follow the pattern you've given), but will be in another column:

=IF(NOT(ISNUMBER(A2)),LEFT(A2,2)&"-"&MID(A2,3,3)&"-"&RIGHT(A2,3))

You would use something similar in a macro. Hope that helps.