Custom Format

[Nick]

Hi,

Done a bit of Googling but no luck so far...

What I need to do is create a Custom cell format for the following:


xxx##-########

Where the x's are any letter and the #'s are numbers. For example, if the user inputs:

SHA11-1234

The Cell will automatically be formatted as:

SHA11-00001234

Can this be done?

Cheers,
Nick

 

[Dave Peterson]

Not by using number format. (Number format works with numbers--not text.)

But maybe you could use a helper cell to translate it:

=IF(A1="","",
Upper(LEFT(A1,6))&RIGHT(REPT("0",8)&MID(A1,SEARCH("-",A1)+1,255),8))

(all one cell)

 

[Nick]

Thanks Dave - shame about that though... maybe I'll just use my old format function in VB...

Cheers!
-Nick

 

[Dave Peterson]

You could use an event macro that actually changes the value.