How Can I Look For A Character Or An Operator In A FORMULA
Jul 9, 2008
Iīm trying to find a way to get a formula which can look if there is a character (letīs say it is "-") in a formula which is, by the way, variable (letīs say each cell has different values like: =899+59-6 and so it goes and goes), and brings me a true or false value.
Specifically it is like this:
I have formulas to be evaluated like the following:
=899+59-6
I need a formula which can look into the formula and if it finds the character "-" or a "+", brings me a true or false message.
When I run this code it does the exact opposite of what I want it do (if not contained in that character list then delete). So basically if it not in the character list then delete, but it if is in the character list it deletes....
I have a date in cell A1. I have an operator in cell A2. This info is then merged in to cell A3 with the following formula: =A2&TEXT(A1,"dd/mm/yyyy"). My data range is called "wc_date_of_loss". I want to use this info in a SUMPRODUCT formula (as the dates and operators can be changed by the end user). What's the correct syntax for that?
I am using vlookup to get a cell value from another sheet, but if the cell has "&" or "/" I need to substitue "&" and "/" with "and" so that the cell can be added later to a url.
i am using =VLOOKUP(a1,Sheet2!A1:W17968,6) to get the value of a1 in sheet 2 and return the value of column 6
this will return "Audio Cables & Leads" but i need it to say Audio Cables and Leads
I need the formula to also check and replace "/" with "and" as well so cables/wire will be Cables and wire
I have on occaision seen -- within a formula. e.g =SUMPRODUCT(--(PMonth=$A3),--(PName=$A$1),PLTime). Could someone explain the relavance/effect or use of this as it seems outside of standard operators.
Excel has operators for Less than, Greater than, Equal to, etc., etc. Does anyone know if there is a Between operator? (Maybe an undocumented or little-known feature like DATEDIF)?
There is a Between Feature in the Conditional Formatting Dialog Box - it would sure be nice to be able to use it in a non-formatting formula, or maybe you could explain how you pros achieve a "between" result without resorting to VBA.
I want to count the number of cells in column B that contain the start with the string US/IL. Here's the formula that I created, but it returns a value of 0 instead of 590.
Is there something I need to do differently when a cell value contains the / character in the string?
=SUM(COUNTIF(B5:B1830,"US/IL"))
Once I have the correct formula for the above, I want to write another formula on the row below that counts how many rows of this 590 value have a corresponding "Yes" value in Column M.
I am working with a tab delimited file, and am needing to have a column of its own that will pull the first letter of another column. i.e. in A will be the title of the book, and in column C I need to have the first letter of the title of the book appear for other uses.
revised this code to reflect the "not equal to". I want the user to only enter "In" or "Out." Therefore, If cells b19:B26 does not have either word, then the message box will prompt the user to enter the correct word again. I think my code needs to be revised into some kind of loop.
i need a formula that will remove the first 2 characters and the last character from the below, so below the result should be R0131644, the number of characters vary from row to row, they are not always 11
While proof-reading my code, I've noticed that these two true select cases ("S" and "W") shown below wouldn't "yield" what I want. I've tried to replace AND (highlighted in red) with "," (comma) but I still get the same result - it still returns a value evaluating either one of two conditions but not BOTH:
Code: sChar = Split(Waypoint, ",") For j = LBound(sChar) To UBound(sChar) Step 3 Lat = sChar(j) Lon = sChar(j + 1) Elev = Val(sChar(j + 2)) Next j
Can you use a variable as an operator? For example:
Dim op op = ">" If Var1 op Var2 Then MsgBox "It Works!"
I have parameters stored in a database and each parameter includes an operator. When I compare a given value to the parameter, I was hoping to do that dynamically by extracting the operator from the parameter and using that in the comparison. Otherwise, the only way I can see to do it is create a Function with a Select Case list for all the operators to check the parameter, do the comparison, then return the value to the Function.
I'm helping out a community that puts out a role playing game by making a character sheet for their gaming system. It's normally a pen-and-paper type affair, but a few people have reached out to me and asked me to make it. Now, one thing that I really thought would be nifty would be to make it persistent. I know of the Excel basics, creating basic formulas and the such, but this is where I hit a brick wall.
For the character sheet, they normally have basic attributes on a 1-20 scale. These attributes are accomanied by a bonus. The only thing is those, while these bonuses go across a linear path (For attribute 1, it's -30, for 2 its -20, for 3 its -10, for 4 its -5 for 5 its 0....), I cant seem to figure out how to create some kind of If/Then formula for it....
Heres my example:
Brad makes a character that has a score of 9 for one of his attributes. The bonus given to someone with a 9 is a +10. What I'm looking to do is create a table that sees that 'If (c9)=9, Then (d9)=+10'...
And my second related issue...
Is there any possible way to do the above mentioned formula in relation to a drop down selection bar? It's the same thing here; To break it down simply, It would be excellent if there was a way for it to say If (a10)=Warrior, then (c10)= +10.
Row 2 to 5 are of same item no. The result that is required is in Column C. The formula to check same item numbers in column A and give the result as "FRA" in column C3 with C2, C4, C5 showing as not applicable. Similarly for the next item no
Is there a fomula that can add trailing spaces. For example....I need a certain feild to be 11 Characters in length. So lets say in Column A, I have a bunch of different words:
House Bird Up Down Yes No
I would like to put a formula in column B that takes my original text and adds blank spaces to the end of it until it's 11 Characters. So "House" in Column A would be "House " in column B.
The usage of the wild card character is not getting me the results I need for a formula I am using at work. When the wild card asterisk character is removed, I get the correct result but only for an exact match of the particular text. I need it to match the "Particular Text plus another text. The Text is CBKC. Then there is also CBKC 5400 and so on. I need to have the wild card expression character before and after the CBKC characters. The formula is below that I used.
Excel doesn't like the way I am expressing the wild card inside the double quotes......the column getting added if the matches are found is column K. The other columns have to meet the criteria in the formula.
filter a pivot table that contains these 3 column fields: User 1, User 2, and User 3.
However, when I want to see the results for "John" in all those fields, I need to use one filter at a time. Is there any other way to see all results of the Pivot Table where "John" is a User, no matter if he is the 1, 2 or 3 user?
I dont have much experience with VBA and im looking for an operator that helps me find a word (or part of a word) in a sentence located in a cell. I have to do this to create a user friendly form. I cant use the Find option in excel because i need to do this in a form.
I need a formula which can pick out the names from string of text, each name follows "-->", the number of names in each cell differs and is undefined. The result needs to be posted into each column following these cells.
An example of a cell is below:
"W/L FIGHTER Str TD Sub Pass EVENT METHOD ROUND TIME
Loss -->--> Tom Aaron -- -- -- -- Strikeforce - Hen... U. DEC 3 5:00
-->--> Matt Ricehouse -- -- -- -- Dec. 4, 2010
Win -->--> Tom Aaron -- -- -- -- Strikeforce - Hea... SUB 1 0:56
I have tried using text to columns but everything after the first line is not recognised (when I click finish anything after "TIME" is simply not there).
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?
Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.
I'm just trying to add the "=" operator to the below "" operators, as this line of code presently doesn't account for any numeric matches, of which I have plenty, and DO need to account for! Gosh, might someone here know how to integrate such a thing into this code?
