Hi. I searched for this but couldn't find anything. I have a list of MAC addresses that i need to format to import into a database. Currently the data looks like this: C8BCC8C5896B They need to look like this : C8:BC:C8:C5:89:6B How do i format the cells to change this? Is it even possible? Thanks in advance. Subscribe for Weekly Excel Tips and TricksHelpful tutorials delivered to your email!Similar Topics
I have a large database with names, addresses etc. When I try to make changes to the email address, like change the font color or change on letter, Excel tries to email the address. If I click in the formula bar to make the changes, it automatically changes it back to all blue text and underlined. I have tried to format all of the cells as 'Text' to show as entered, but it doesnt work. PLEASE HELP
Hi All, I've been struggling with this for a while now and can't believe how hard it is!. I've searched on this site and on others to get some clarification but to no avail. It's pretty simple really. I have a user form which contains a tex box for a user to input the date I want the format to be dd/mm/yyyy but can't find out how to set the format of the text box to this. Please can someone give me a bit of guidence or link me to a good rescource if missed it in the search. Thanks in advance. G
Hello. I have two huge excel files, with many rows and columns, they 'should' look the same. But I need to find out if anything has been added or removed from the second one compared to the first one. Which is the easiest way to accomplish this? Searched this forum and Google but couldn't find what i was looking for. Thanks in advance! Hi, this is something so simple that I have forgotten how to do it. I want F3 to turn yellow if someone selects 'Yes' from a drop down list in cell E3, then F3 has no fill once data is entered into it. And F3 turns Black if E3= no selected from the list. Or F3 turns red if E3 contains 'TBC' so in summary, if E3='Yes', then F3 conditional format to Yellow until data is entered in it. if E3='No', then F3 conditional format to Black if E3='TBC', then E3 & F3 conditional format to Red until E3 is changed to 'Yes' or 'No' at a later time. Thanks in Advance for the help Mutley13 I am looking for assistance in having one cell in a text format equals another cell that contains a time value in hh:mm format. For example: Cell A1 has a time format (hh:mm) value of 04:00; which is the Start Time. I would like cell D1 to have a text format value of '04:00' (result is dependant upon what is entered in A1). I would duplicate the same formulas to reflect Stop Times in other cells. My final result is to have another cell (F1) use the Concatenate formula to have the Start and Stop time shown in one cell as '04:00 - 12:30'. The times would change based on the Time formated values entered into the Start and Stop time cells. I have researched this in the board and found many excellent ways to do the opposite, but not convert Time format to Text format. Any assistance is greatly appreciated. -Shane
In excel, I tried to convert numbers to number format, using format/cells/number from the category list. But it won't take, and stays text-like. Any ideas?
Hi I am a new user and trying to setup a database in excel. I can select all addresses and copy but when trying to paste into excel it does so in 1 cell. Is there a way I can paste the separate email id's into individual cells so I don't have to do it individually? Many thanks Patrick I'm trying to use conditional formatting to highlight phone calls that came in between certain hours. The call times are in the custom format h:mm, although it could easily be changed to an Excel time format. I'm using Excel 2002. I want the cell to have a different fill color if it falls within particular time frames. Example: If the call came in between 22:00 and 23:59 color is light green. If the call came in between 23:59 and 08:00 the color is yellow. Example spreadsheet is attached. Thanks in advance. I have imported a DBF file into Excel and have a column of dates that are missing the leading zero on single didgit months. When I try to us the custom format of mm/dd/yyyy it doesn't work (interestingly, after I select that format if I click on an individual cell it changes to the right format). Does anyone know a better way to do this? Thanks in advance! Hi all, I have several accumulated values from other spreadsheets on my spreadsheet and I'm combining and organizing them on mine. However, the cell alignment isn't working properly as you can see in the picture below. Now, I can certainly retype these values and align them and they'll align all nice and neat; however, I have a huge Column and retyping that column would take an inordinate amount of time. I searched earlier on cell alignment and couldn't find anything. Any help is appreciated. Sincerely, Bill
i have a cell i have to check if it contains six characters. I have a list of data that i need to narrow down to six characters. I have successfully done that, but some of the cell has 5, 6, or 7 characters. The list contains about 600 cells, but i don't have time to format them individually. I want to create a formula that returns true or false if the cell contains 6 characters and false if it is above or below 6. Can somebody help me, i'm new to excel!
Dear Sir, If any one could give me a solution for this its will be a great help for me. I am working in a bank and on every day we receive Month to date data of Loans and advances made by every branch with Region wise total and manager wise total. With Sum of loan and count of loan. In our MIS format the in A coulum branches are sorted and listed in a sequence with sub total Region. I have data where if if do the pivot and change the data the out put should be displayed accordingly in the MIS format. Can any one help me how can we do this or is there any other office addins/softwares are available if so the data is changed on a daily basis and if we do the pivot it will automatically copy the data to the MIS format. Thanks. I have and excel sheet that was a contact list apparently exported wrong and has the names and addresses all together in each cell in about 350 rows in column A, they are all in this format john doe email address here for some reason the email address in brackets won't show up here but each email address is in brackets I need to get the name in one column and the email address without the brackets into another, any ideas?
I have an extensive spreadsheet with several columns and all showing borders where each cell/column begins and ends. However there are a few cells where the break with the next cell does not show the line. I have highlighed the cell in question, gone to format/cell/borders and everything looks fine. Black color + format border shows square with all sides of square showing. Any thoughts on how to fix these few random cells. Thanks
Hi all, I've had a long search through your pages to see if this question has been answered before but having browsed through about 50 pages worth of threads I couldn't see anything, but if I am repeating prior information I do apologise. I've written a macro that is relatively simple. It just takes some information in one format, rearranges it, adds some formatting and performs some calculations. Nothing incredibly fancy but it works fine on my computer. Now, I need to share this macro with some other people, so basically I've just sent that excel file on to the people that need to use it. Should be fine and in most cases it is, however there is one user who although they can open the file, can't seem to get the macro to run properly. It seems to get a small way through the macro but then stop with no error messages or any sign that it hasn't completed properly. I have checked Macro Security level and that is the same as mine, Tools - Add-Ins is the same, In Visual Basic, Tools - References is the same as mine. It is the same Operating system and the same version of Excel. I have even signed into this person's computer as myself (it's a big company network thing) and tried to run the macro and it works fine, so there is nothing wrong with the hardware. I've googled and searched and tried everything I can think of but I'm no closer to solving this problem, so if anyone has read through this wall of text and can come up with a possible solution, that would be greatly appreciated to save me from tearing ALL my hair out! Thanks very much for your time. Hi all, I hope you can help me with what I would like to do. I've searched and searched and not come up with anything yet!! I have one sheet which is my 'data' sheet with a number of columns, one of which is a date which may be repeated. I then want to use another sheet with two cells where two dates can be entered and a button which allows you to copy the rows of data from the data sheet which have dates between the two specified originally. The list in the data sheet may not be in date order although I could update a macro to do this for me if needed. I would also like to do it so that if 'ALL' is typed in the date fields, it pulls over ALL the data. (Or something similar) Any help you can give would be much appreciated. Cheers, Kevin
Hello All, I have a column where cells need to be 50 characters long, it contains addresses, however, since all the addresses do not meat the criteria, I need to add (blank spaces) to reach the 50 characters, any idea on how to approach this? Example Fire Street #50 this cell contains 17 characters therefore I need to add the rest by adding blank spaces to reach 50 characters, otherwise the system I am exporting the data will not understand this cell
How do I get the colors to change automatically when I use a drop down list. Per say here is what I am trying to do If a certain 'word' is used from the drop down list I want the background of that cell to change its color to 'green.' And if later I change the 'word' to another from the drop down list, it will change it's color to a specified color. The drop down list that I use was created from cells that have the colors already in the 'word', but I do not know how to make the list show the colors so it puts the word & color automatically in the drop down list to the cell with the drop down (if that makes any sense). If there is a way, please spell it out simple enough for me to understand, as I don't know fully the capabilities of formulas or vba.
Hello, I am copying charts to display the same information for different regions. When I work on the copied region, I find it a cumbersome task to go through each charts source data and change the cell references to the different region. Essentially I am doing a trending analysis for each region, with a region having its own sheet with 5 charts per sheet. What I have done is copy the original sheet and am updating the sheet for another regions source data by going to the source data. The source data is all in the same row/column format, but each regions source data has its own sheet. Is there a more automatic way to do this? Perhaps a way to update all 5 charts references at the same time. Does anyone know if there is a customer format I can use to change the percent value of a cell to basis points? I tried creating my own, but was having difficulty. For example, if the cell value in A1 is 2%, I would like to have it say 200 basis points instead. Thanks.
i want to retain the data and format, but get rid of the pivot capacity before sharing the spreadsheet. right now if a copy/paste special, i can get the data, but not the formats, any suggestions? Hi there, I have a spreadsheet with some cells setup with a drop-down list containing Y, N or N/A This is being used on a TabletPC but if I make a mistake or need to change back to a blank field I have to invoke the soft keyboard, activate the cell and hit backspace then close the soft keyboard - quite a long-winded procedure just to change an incorrect choice! What I would like to do is add a blank to the list so if I have to revert back to a blank I can just use the stylus to choose a blank from the chooser list. How do I add the option of inputting a blank from the Data Validation List bearing in mind I am using the Data Validation Source box for entering my choices directly and not specifying a range of cells? I have tried adding ' and even a space to no avail. Although not a betting man I would wager there is a simple 'fix' for this but things are only simple if you know how in the first instance! ;^) -- Thanks & regards, -pp- In Excel there is a difference between cells that are blank (= ') and that are empty. You can make blank cells empty by selecting them and pressing the delete key, but I need a fast way to do this on all blank cells in a large worksheet. The only way I have found is with a VBA macro that loops through every cell, tests for '.Value = ' ' and then uses the '.Clear()' function, but doing this on 30 columns x 10000 rows is far too slow. Any solutions? (The reason I need to do this is for importing into Access, the database treats empty cells as NULL which is what I want. Blank (but not empty) cells screw the import process up.) I am creating a data sheet to be completed by other users. I would like to format the text cells (name, etc) to have text entered as uppercase automatically although the user might use title or lower case. UPPER function cannot make cell look at itself and perform the function Excel 2003 Hi, Try this... There are some valid datas in the cells A2 (Eg. 100), B2(Eg. 200), C2 and D2. Data validation is used in cell K2 to limit it to a set of values. Can we implement this formula? IF(K2='Approved') { A2=A3; B2=B3; C2=C3; D2=D3; K2=K3; } ELSE { no change to any values. } Thanks in advance... |