Ask Experts Questions for FREE Help !
Ask
    kim31's Avatar
    kim31 Posts: 1, Reputation: 1
    New Member
    		  
    #1

    Jul 30, 2008, 02:15 PM
    Format Cells in Excel 2007
    Is there a way in Excel to automate adding hyphens in a specific position in a column of numbers
    (like a mac address list)
    For example: 0021e96c4183
    00-21-e9-6c-41-83
    HOWEVER
    When I use this method Home tab, Cell group, click on Format
    On Number tab from the Format Cells dialog screen I'd select Custom and type on General text box this: 00-00-00-00-00-00
    It does not enable that particular format for me but on my supervisor's computer she got it to work when it didn't work in the past for her
    She said she didn't do anything special at all just keep trying and suddenly it worked for her

    I have spent hours and days trying to figure this thing out and I can't
    I've tried it on different computers too
    Could it be a bug in Excel 2007? Why does it work sometimes and it doesn't at other times?:confused: :confused: :confused:
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    Jul 30, 2008, 04:36 PM
    I can't figure how to do that in a single cell AT ALL. I'd just use 6 adjacent cells. If for some reason you NEEDED to see it all in one cell, you could then concatenate the 6 cell entries. THAT would work.

    =A1&"-"&A2&"-"&A3&"-"&A4&"-"&A5&"-"&A6
    KISS's Avatar
    KISS Posts: 12,510, Reputation: 839
    Uber Member
    		  
    #3

    Jul 30, 2008, 05:31 PM
    I'd just use 2 cells, the 1st celll would be the one without hyphens and the second would have a formula like

    =MID(A1,1,2)&"-"&MID(A1,3,2)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2)

    EDIT: Fixed formula
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #4

    Jul 31, 2008, 01:12 AM
    You could keep the sheet clean in appearance, extending the suggestion above, by having the two cells side by side, but the cell you enter data in is tiny, and formatted to show WHITE text, making your entries basically invisible, and then the wider attractive cell to the right with the formula above.

    You could use VALIDATION and cell locking to keep people from entering text in the second cell, and validation errors you get to word the error message, so you could pop it up to say, "You must not enter anything in this cell. Please abort and enter your MAC address info in the cell to the left."
    mdosh01's Avatar
    mdosh01 Posts: 64, Reputation: 8
    Junior Member
    		  
    #5

    Jul 31, 2008, 04:34 AM
    Custom formatting only applies to numbers, not text. I have to assume the reason it works some times and other times not, is because if the entry is "0021e96c4183" contains text. Whereas if the entry is "002139624183" it works fine because it is all text.

    I tried this in Excel 2003 and verified the results. Maybe 2007 does have custom text capabilities.
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

How to count the cells in excel with different fill pattern styles [ 2 Answers ]

Hi I want to count the cells in excel with different fill pattern styles. How is it possible. Can anyone post the function for this purpose like counting coloured cells. I tried a lot , but I got failed. Here I got a function to count cells filled solid, but it is not working... is it...

Memory Usage in Excel 2007 [ 2 Answers ]

I am trying to do simple operations (multiplication, division, cut, copy, paste, insert columns, etc.) in a large database in excel 2007 (500,000 rows x 60 columns). However, my XP machine (1gb) gives an out of memory error - I have stopped all unnecessary programs but this message keeps...

Excel - Merge Cells [ 3 Answers ]

Any way to merge a number of cells into one cell, for example the title of many columns in a table?

Multi line cells in excel [ 4 Answers ]

Hey guys, any idea how to have multiple lines in a single cell in excel? I'm trying to make my table look a little neater, but it makes some of my comments overlap into following cells - is there any way to make the rows higher and to have 2 lines of text in a cell? Thanks in advance - Cap

Excel Format [ 7 Answers ]

I need someone help. Everyday I need to format data like the following 1003, 1876, 1952, 2011, 2048, 2057, 2073, 2094, 2111, 2128 This gets pasted into one cell in a spreadsheet. I would like to be able to format it so it goes into the sheet 1003 1876 1952 etc into separate cells. Is this...

View more questions Search