Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Cell Format Will Not Save Correctly | Excel Worksheet Functions | |||
Custom percent format needed | Excel Worksheet Functions | |||
Cell will not format numbers correctly for a 13 digit custom barc. | Excel Worksheet Functions | |||
Keep custom format in new worksheet | Excel Discussion (Misc queries) | |||
How do I format a cell for a custom part number? | Excel Discussion (Misc queries) |