Custom Format
 

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

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 wrote:

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

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

Cheers!
-Nick

"Dave Peterson" wrote in message
...
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 wrote:

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

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



Nick wrote:

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

Cheers!
-Nick

"Dave Peterson" wrote in message
...
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 wrote:

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

--

Dave Peterson