I'm trying to figure out the whole Regular Expression thing...so I figured Hotpepper's EXDATA UDF would be a good place to try and rewrite with Regular Expression...I got it to work...but not sure why as it seems opposite of what I would have expected...
eg pattern [^/d] i thought would give me everything except numerics...but it appears to be giving me numerics....
here's the code and sheet...can someone help explain what's going on? .......
a) The first letter cannot be number b) the password should have a combo of numbers letters and one special character c) The password should begin with a big case letter
I am using the following to check if a string has a sequence of 6 decimal digits in it. But am getting an error. If(str Like *######*). I have to check if str has values like 123456USA ; ABC725439 ; jh658478hd. I thought # represents a single digit and * represents any no of characters
I've some code that I use to scan HTML to find images. The basic part string I use is:
WildSought = "**"
The problem is that there are several ways to close an image tag. The above closes using /> but you could also use "> for example. Is there a way in my regular expression to say "this OR that". Maybe it would look something like:
I've used LOOKUP in the below and it works great. LOOKUP(C8,{"A","A-","A+","B","B-","B+","C,","C-","C+","D"},{9,8,10,6,5,7,3,2,4,1})),)
But I've not been able to use that knowledge (or Excel help) to get the following to work.
LOOKUP(D108,{<1,>=1,<1.25,>=1.25,},{"D","C","C","")))...etc. where D108 is in the range <1.0 to >2.0, and the letter grade must correspond to D108 to Letter Grade <1.0 D 1-1.25C 1.25-1.5B 1.5-1.75B+ 1.75-2.0A >2.0 A+
Is there any formula or easy way to automate the changing of a numeric input into words, eg if you input 100 in one cell, the output in another would be "one hundred"?
working with a macro to auto filter out all NON-NUMERICS in a column chosen by the user. heres the code i'm working with now:
Sub DeleteAllAlpha() Dim rTable As Range Dim lCol As Long Dim vCriteria On Error Resume Next With Selection If .Cells.Count > 1 Then Set rTable = Selection Else Set rTable = . CurrentRegion On Error Goto 0 End If End With If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction. CountA(rTable) < 2 Then............................
I want to count the number of cells in a row containing a value greater than a fixed number but less than a number entered in another cell.
For example, in the cells A1..K1 I want to know the number of cells which have an entry greater than 7 but less than the value I have entered in cell M3. I have used the COUNTIF in other circumstances but I cannot find out how to use it with this "greater than and less than" condition?
I'm trying to make an expression to calculate some fields in Excel 2007. Trying to check fields up to a given number and give them a high multiplier, and add the rest of the numbers with a lower multiplier.
I have multiple fields with numbers in hundreds. I want to multiply the sum of this fields (up to 500) with 4. But the expression must also start the count from the first field, and when im hitting 500 as a sum I want the rest to be added with 2 as multiplier (the reason for the prioritizing of the fields is that they also have their own values/multipliers, but I'll skip that to avoid to much confusion here).
It's probably a lousy description. I'll give an example:
Field 1__Field 2__Field 3____Sum 100____300_____500______2800
How in earths name did I get that Sum you might ask. ((100 + 300 + 100) * 4) + (400 * 2) = 2800
400 is the number left from the third field wich I only want to multiply with 2 instead of 4.
Another example:
Field 1__Field 2__Field 3____Sum 300____600_____500______2000
((300 + 200 + 0) * 4) + (900 * 2) = 3800
900 is taken from the rest value of field 2 and 3.
If there any way of doing this "sum check and multiply, and then add the rest with a different multiplier"?
With Excel you can do If(array, array2, false) to poplulate another array. Is there a way to replication this without looping. For instance can the IIf hold an array as its expression or is there any other way of replicating this If statement without looping?
Here's what I need to do. I have a list of row numbers in AA2:AA6, so for i =1 to 5 I want to insert a page break before cell (column AA and Row(#in cell AA2-5))
I am currently using a SUMIF statement as follows: =SUMIF(W1:W1500, "Charnwood" & "*", S1:S1500). The problem i am having is the fact that SUMIF doesn't work with a reference to a closed worksheet. I tried using SUMPRODUCT but it did not work. The reason it didn't work is because I need to find in column W anything with Charnwood and anything else past it, auch as Charnwood Estates. This is what I tried: =SUMPRODUCT((W1:W1500="Charnwood")*(W1:W1500="*")*(E1:E1500="S"),S1:S1500) which of course is a syntax error.
I have been reading the different post here on the forum. I have found a snippet of code that will open a pop-up message from a value of a cell. I would llike to do this on one of my reports. Here is the scenario.
Range (R5:S5) 0.15 them MsgBox "Note Must Be Entered For +/- 15% change" But There are 14 different cells that I want this to work for. The value differs from day to day, and this should pop-up happen without a user having any actions other then the calculated value. But ones the note is entered in the "Notes: cell the message will not return. Hope I explained it correctly.
The value of the cells in question are calculated in each cell with a formula example "=IF(ISERROR(Z20/H5),0,IF(ISERROR(Z20/H23),0,(Z20/H23)))"
I have a formula that has been working and it looks like this
=VLOOKUP(A13;'IFS export'!$A$1:$F$19000;4;FALSE)
Now I want to use the same formula in an other workbook and it gives an error
=VLOOKUP(A2;DRAWING!$A$2:$C$9168;3;FALSE)
When I type this formula I get the window that says "This formula contains an error. *For information about fixing common formula problems, press help. *........... *..........."
And if I go and try to change the first formula that has been work it gives me the same error.
I have a large spreadsheet in which I have reached the limit for conditional formatting. (i.e. 2050 rows)
To resolve this I can add a single expression for conditional formatting across all cells. This means I need to create a more complex expression.
I effectively have two types of conditional formatting that are very similar. I have the standard Yes, No & A/L which I wand to shade accordingly. I then have alternating columns of Yes , No , A/L . I have introduced spaces to stop these values from being counted by another formula but still display yes and no. I want the shading to be the same in both cases.
I have built the following expression which does not seem to work.
I'm trying to do a COUNTIF using regular expressions. I can do it in SQL, but I'm having a hard time figuring it out in Excel.
My SQL query looks like:
[Code] ....
In Excel, I'd like to do the same thing so that I can send it to a client and have the counts already in the spreadsheet. Basically, I want count anything that has 5 numerical digits followed by "_.....".
Hope the title isn't too broad. Every time I think I have got to grips with RegExp I get stuck on something apparently quite simple. So, I have a few queries so I hope OK to bundle them together.
1. How do I remove the word 'of' only if it occurs after certain other words (the other words should also go). E.g.
"Vale of White Horse" should stay the same "Borough of Lambeth" should become "Lambeth" (because 'of' after 'Borough')
I tried "Borough(sof*)" but that doesn't remove either word.
2. How do I remove everything after a '/' (inclusive)? I have tried "/sw+" which removes the first word after '/' but there could be other words
3. How do I remove everything inside brackets (inclusive)?
I am creating an electronic timesheet. I have created drop downs for times of the day, which then automatically calculates the number of hours worked based on the times selected. The trouble I am having is, I also need to incorporate "Vacation", "Sick Day", etc into the drop down box where the time of day is located.
The sheet works great until someone selects a non-time related answer, such as "Vacation". When this option is selected, the cell that is supposed to contain the number of hours worked says #VALUE! when I, in fact, need it to list a value of 0 for the number of hours worked. Any idea on how to combine my formula =(D6-D5)*24+(D8-D7)*24+(D10-D9)*24 with an IF statement that will automatically assign a value of 0 if a text answer is selected from the drop down?
