How can I substitute quotation marks in Excel with SUBSTITUTE formula?
“””” escapes a “: =SUBSTITUTE(SUBSTITUTE(B1,” “,””), “”””, “”)
worksheet-function
Convert date field into text in Excel [duplicate]
You don’t need to convert the original entry – you can use TEXT function in the concatenation formula, e.g. with date in A1 use a formula like this =”Today is “&TEXT(A1,”dd-mm-yyyy”) You can change the “dd-mm-yyyy” part as required
How do I slice an array in Excel VBA?
Application.WorksheetFunction.Index(array, row, column) If you specify a zero value for row or column, then you’ll get the entire column or row that is specified. Example: Application.WorksheetFunction.Index(array, 0, 3) This will give you the entire 3rd column. If you specify both row and column as non-zero, then you’ll get only the specific element. There is no … Read more
Get values from other sheet using VBA
Try ThisWorkbook.Sheets(“name of sheet 2”).Range(“A1”) to access a range in sheet 2 independently of where your code is or which sheet is currently active. To make sheet 2 the active sheet, try ThisWorkbook.Sheets(“name of sheet 2”).Activate If you just need the sum of a row in a different sheet, there is no need for using … Read more
Case Function Equivalent in Excel
Sounds like a job for VLOOKUP! You can put your 32 -> 1420 type mappings in a couple of columns somewhere, then use the VLOOKUP function to perform the lookup.
Use string value from a cell to access worksheet of same name
You can use the formula INDIRECT(). This basically takes a string and treats it as a reference. In your case, you would use: =INDIRECT(“‘”&A5&”‘!G7”) The double quotes are to show that what’s inside are strings, and only A5 here is a reference.
Concatenating date with a string in Excel
Don’t know if it’s the best way but I’d do this: =A1 & TEXT(A2,”mm/dd/yyyy”) That should format your date into your desired string. Edit: That funny number you saw is the number of days between December 31st 1899 and your date. That’s how Excel stores dates.
Excel formula to get cell color [duplicate]
As commented, just in case the link I posted there broke, try this: Add a Name(any valid name) in Excel’s Name Manager under Formula tab in the Ribbon. Then assign a formula using GET.CELL function. =GET.CELL(63,INDIRECT(“rc”,FALSE)) 63 stands for backcolor. Let’s say we name it Background so in any cell with color type: =Background Result: … Read more
Excel Reference To Current Cell
Several years too late: Just for completeness I want to give yet another answer: First, go to Excel-Options -> Formulas and enable R1C1 references. Then use =CELL(“width”, RC) RC always refers the current Row, current Column, i.e. “this cell”. Rick Teachey’s solution is basically a tweak to make the same possible in A1 reference style … Read more
How to turn a string formula into a “real” formula?
Evaluate might suit: http://www.mrexcel.com/forum/showthread.php?t=62067 Function Eval(Ref As String) Application.Volatile Eval = Evaluate(Ref) End Function