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

[Nick]

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

Cheers!
-Nick

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message
news:43009083.BBBDFA2C@verizonXSPAM.net...
> 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]

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" <petersod@verizonXSPAM.net> wrote in message
> news:43009083.BBBDFA2C@verizonXSPAM.net...
> > 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