Format a number

[jlo]

Is there a way to format a number 00000000000 to '00000-000000',

I need the quotes, dash and comma.

I found a style to work but I have to click on each cell to see it change.

 

[Sean Timmons]

Format Cells > Custom

'00000-000000'

Voila

 

[JoeU2004]

Format Cells > Custom
'00000-000000'

Forgot the comma. Simply append it to Sean's custom format, to wit:

'00000-000000',

But that presumes that "jlo" has the number zero (or any other number) in
the cell to begin with. I'm suspicious, given that we usually advise people
to enter strings of digits as text (with apostrophe prefix or by
preformatting the cell as Text) when the digit string is not intended to be
used as a number (e.g. credit card number).

"Jlo", are you saying that you want to enter a number (e.g. zero) and have
it appear in the form '00000-000000', (with comma)?

Or are you saying that you see 00000000000 in the cell, and you want it to
appear in that form?

If the latter, is the cell value numeric or text? For example, does
=TYPE(A1) result in 1 or 2?

If Sean's solution does not work for you, the reason might that the cell
contains text, one way or another.


----- original message -----

 

[Sean Timmons]

Thank you for the clarify.

Forgot the comma. Simply append it to Sean's custom format, to wit:

'00000-000000',

But that presumes that "jlo" has the number zero (or any other number) in
the cell to begin with. I'm suspicious, given that we usually advise people
to enter strings of digits as text (with apostrophe prefix or by
preformatting the cell as Text) when the digit string is not intended to be
used as a number (e.g. credit card number).

"Jlo", are you saying that you want to enter a number (e.g. zero) and have
it appear in the form '00000-000000', (with comma)?

Or are you saying that you see 00000000000 in the cell, and you want it to
appear in that form?

If the latter, is the cell value numeric or text? For example, does
=TYPE(A1) result in 1 or 2?

If Sean's solution does not work for you, the reason might that the cell
contains text, one way or another.


----- original message -----

 

[pogiman via OfficeKB.com]

I'll assume that you want to divide an 11 character content into 5 and 6 then
this would be my formula:

Say the content is in cell A1
=if(ISNUMBER(A1),("'"&text(A1,"00000-000000")&"',"),("'"&right(A1,5)&"-"&left
(A1,6)&&"',"))

Value are converted to 11 character text while text must be 11 character in
length. If you're working on number/value only, just use this ="'"&text(A1,
"00000-000000")&"',"