Excel Help - formatting cell & coding?
488375
Hi Casp911\n\nI have not seen the formula spaces anywhere.... the formula is REPT.. .so, taking the example of Dragon Dave\n\nH6&Rept(30-LEN(H6))\nthe question here is if you are exporting the info from Excel or importing directly from the other program... if the other program is a microsoft one, you can solve your problem using a simple macro\n\nlet me know if i can be of further help
366534
It completely depends on where you are using this formula, is it in Excel? A program? What language?\n\nWe need more info.
1186574
Format the column as "Text" instead of as "General". When it is input as a number, it is limited to the precision of an integer or double integer. When it is text, any math performed on it will be at the limit of precision, however the digits will display on the sheet.\n\nIf you are typing in numbers, you can force the cell to be treated as text by typing an apostrophe (') first.\n\nInformation about how Excel handles numbers can be found here: http://support.microsoft.com/kb/78113/en-us
991232
You can use a VBA sub with a very simple "Select Case" construct to define color depending on the value.\n\nAdd the following macro to your personal workbook (Alt-F11, right click the Personal Workbook in the VBA project window and select "add module" then paste the snippet of code in the main VBA window)\n\nWhen you want to implement the formatting, just select the range that needs to be conditionnaly formatted and execute the macro.\n\nAs it is, the macro evaluates a cell value and give to its immediate neighbour on the right the associated color. If you want to change the relative positions between evaluated cell and formatted cell, just change the values in all the Offset() of the code. The first argument is the row difference and the second the column difference.\n\nThere is no limit in the number of conditions you can use, just add more "Case" statements mimicking the syntax used here to increase the granularity of your conditionnal formatting.\n\n\n\n\nSub ColorCoding()\n\nDim rngCell As Range\n\nFor Each rngCell In Selection\n\nrngCell.Offset(0, 1).Interior.ColorIndex = xlNone\n\nIf IsNumeric(rngCell.Value) And Len(rngCell) > 0 Then\n    Select Case rngCell.Value\n        Case Is > 4\n            rngCell.Offset(0, 1).Interior.Color = vbBlue\n        Case 3 To 4\n            rngCell.Offset(0, 1).Interior.Color = vbRed\n        Case 2 To 3\n            rngCell.Offset(0, 1).Interior.Color = vbYellow\n        Case 0 To 2\n            rngCell.Offset(0, 1).Interior.Color = RGB(255, 140, 0)\n    End Select\n\nEnd If\n\nNext rngCell\n\nEnd Sub
411443
this is the best Excel site I've ever found on the web. Lots and lots of tutorials from beginner level to expert. \n\nhttp://www.MrExcel.com\n\ngood luck
697117
Subtract the cells and format it as numeric
511921
You  will have to reformat all the cells you need to be exported. Excel will not automatically append the double quotes.\n\nUse the following formula\n=""""&A1&""""\nAssuming A1 has 999, the formula will return "999"\n\nThen you can save the worksheet as a text file.\n\nYou can then copy, paste special, values over the original column or to another worksheet.\n\nNote: The formula needs four double quotes before and after the ampersand
705798
Use data validation.  \nSelect the column, choose Data / Validation...., \nchoose "Allow" = "Text length",\n "Data" = "Less than or Equal To", and "Maximum" = 30\nIf you want fo find out how many characters you have in cell:\n=Len(Cell)    i.e., =Len(B1)
524805
I don't think that pasting the code here would work so try the site I used as a source. I'd use tables if I were you though. They're easier to understand. (:
718467
go to the Format column, and then go to Cells...\nIn that box, pick TEXT. it will display your number exactly how it is written.
596850
Do these words have only numbers? or do they have extra letters?\nIf the numbers you need to convert are in the beggining of the cell, use the Value function\n=value(A1)\nassuming that the cell you want to convert is in A1\n\notherwise\nsend me you e-mail address , and I will be happy to help\n\nI am the XLMan
371049
It's quirky, but you can do it...\n\nAll times in Excel are DateTimes, so you have to provide a day. Also, remember that the formatting is important.\n\nIn one cell, put "1/1/2006 9:00 am", in another put 1/1/2006 5:00 pm"\n\nIn a third cell, subtract the second cell from the first.\n\nNow it works, but it doesn't look right because its showing you a date as well as a time. Highlight all three cells, and go to "Format | Cells" and set the format to "hh:mm" Now the cells look like:\n\n9:00 am\n5:00 pm\n8:00\n\nVoila!
894080
-Select the range where you wish to apply conditional formatting\n-Format | Conditional Formatting...\n-Change Condition 1 from Cell value Is to Formula Is\n-Your formula should be =ISNA(cell reference for first cell in range)\n-Click the Format... button and change the color to what you want\n-Click OK (twice)
811386
In Excel 97 (Must upgrade!) ... Format ... Cells ... Number ... Custom.\n\nSelect #.##0.00;-#.##0.00 and edit it to #.##0.00;(-#.##0.00) \n\nIt also saves your new format at the bottom of the list.
1156850
Yes, I know alot about Excel, I am the XLMan\n\nsend me your IM so I will add you to my list and start with your questions.\nRead my profile, I can solve this out and teach you, actually I like teaching Excel as well, so don't worry.\n\nBy reading your question I think what you need is to save the date every day in a cell, well this can be done in some tricky macro, (If you are interesting)\n\nXLMan
347042
I just looked up the Add On.  It looks very good.  I know what might be your problem.\n\n(1)  Click on the cell to edit the formula.  \n(2)  Press Ctrl-Shift-Enter\n\nStep 2 will insert the {} which you need for an array function.  If this does not help not sure what the problem is without seeing the entire formula.
102604
I am assuming that you are using Excel.  Highlight your cells and click Format->Cells on menu bar.  Change from text to Number.