Custom Cell Formatting

[jay86]

Hi there,
I'm new to the board so hi to all. I just had a quick question about formatting a cell. I wanted to see if I could save a custom format for the way a number is displayed in any given cell. In my case I have a 15 digit number that I would like to change to display differently. Of course, I wanted to do this without having to use a macro but I might not have a choice.

An example of what I'm trying to do is change 49033111110000 to display as 049-033-11111

I tried using a macro for this, but I could not get it to apply this same formatting for a range of cells since the range is not constant. What I wanted to see is if there was a way to save a customized format for a cell that could be applied to other cells for later. I know I might just have to tinker around with a macro later, but I wanted to see if something like this is possible with Excel 2007.

 

[asking]

Do this ~ in a spare cell type 10000, copy that cell then select all cells with those numbers you wish to format.

then ~ paste special, values, divide. That gets rid of the 0000's

Then select all cells with your number in them, then in custom format type ~
"0"#"-"###"-"#####

then ok.

 

[c_m_s_jr]

If you do want to attach it to a Macro so you can highlight a range and apply the custom format to all values in the range:

Sub 15_Char_Format()
'
' 15_Char_Format Macro
' Formats Cells to with hyphen
'
'
Selection.NumberFormat = ""0"#"-"###"-"#####"
End Sub

Once you have created the macro you can attached is to a button and then just click the button when you want to use the format.

 

[jay86]

Hey guys, thanks for the help.