I have a little problem in using wildcard character in IF condition.
eg:
--------------------------
For I = 2 to LastRow
If Activesheet.cells(I,5).Text = "ENF*" then
...........................
...........................
...........................
End If
Next I
--------------------------
I want the code to search for any word starting with ENF and then take the necessary action.
Could anyone tell me how to make use of wildcards in a situation like this.
I need to get rid of the questions marks in the text on my sheet, but am unable to get my VBA code to search for an actual question mark and not the wildcard. I'm using the replace function as follows:
wsReport. Cells = wsReport.Cells.Replace("?", "")
1. What's the trick to searching for wildcard characters?
The below is a formula I utilize to collect totals for individual associates.
=SUM(IF('01_05_07'!$C$6:$C$102="Petri, Pete #1",'01_05_07'!$AW$6:$AW$102,0))
What I would like to do is keep this same formula but somehow add a wildcard character so that I could capture all totals for Petri, Pete as there are Petri, Pete #1 thru Petri, Pete #15. Anyone aware how to revise the above formula. I've tried utilizing the * and ? characters but I must be putting them in the wrong place as I am not able to get it to work??
formula to find the last number in a cell and if that number meets a condition put a letter in the adjacent cell.
In cell A1 I have numbers as such 254671 (these numbers vary in length but not normally more than 8 in length). I want to know if the last numeral on the right is a 1 and if so then enter in the adjacent cell (B1) the letter y if not then leave blank.
I am trying to pull certain members loads from our global design force spreadsheet and because of the naming convention we used for our structural members the 2nd to last character is unique to what I am looking for. I am trying to get a simple condition statement that will display the load only if the 2nd to last character of the name of the element is satisfied.
For Example, if "T" is what I am looking for then :
EDT4 will be true and give me the value accordingly D10T1 Will also be true and return me the value D10B2 Will not be true
I know how to use the Mid() and right() function, but I need to check the 2nd from the right.
I currently have a report that I have code on that will change the color of a cell based on whats in the cell.( Code posted below) However I also need to look at $J and if the first letter of the value in that column is a "z" then I would like to change that whole row to icolor 13 (purple).
For example J14 might be "Z-Thomas". if it does I would like that row 14 to change to purple.
Also I would prefer that the solution be in VB, so that we do not have to do something in VB with the fix below AND do something in Conditional Formatting.
Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer
If Not Intersect(Target, Range("$A:$E")) Is Nothing Then Select Case Target Case "y", "Y" icolor = 4 Case "n", "N" icolor = 3 Case "?" icolor = 6
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 have a cell which will contain SER01+SER02+SER03
and what i need it to contain is [SER01]+[SER02]+[SER03]
and shocker is i've got this to work for the first instance but not the other two
code as below... be grateful for your help
Sub measure1() Dim list As String, pos As Integer, refl As String, refr As String, newlist As String list = Cells(1472, 16).Value pos = InStr(list, "+") refl = Left(list, pos - 1) refr = Right(list, pos + 1) newlist = "[" & refl & "]" Cells(1472, 17) = newlist End Sub
I am trying to perform a calculation in a cell that leaves the value 0 if before Feb 2014, changes to the value of another cell in Feb 2014 and leaves the value unchanged if after Feb 2014. I can get to the Feb 2014 value but after that month the value changes to 0.
The Cell formula that I am trying to use is as follows:
I would like to try and achive a conditional format that will require a lookup function. when the condition is met it places Yes or No into a cell and changes that row colour to red for the NO condition. and after 10 days from now the rowcolour is removed.
Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.
I transferring data from the mainframe and some data has ????. I can not replace them with something else because excel think it is wildcard. The reason for this request is I was trying to count the number of cells that has ???? and the count was something different each time. If you want to have fun try it out. It will count ?, ??, ??? but not ????.However my question would be how to change this (????) to some thing like unknown...
For Each rngC In rngJbn rngC = Trim(rngC) If rngC.Value <> newvalue And rngC.Value <> "ab-" Then 'MsgBox "****" & rngC & "***" Résultat = Application.CountIf(Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible), rngC)
MsgBox rngC & " " & Résultat & " " & i End If newvalue = rngC.Value Next
Is it possible to use the wildcard in an IF formula? For instance, I have a spreadsheet with text and numbers in the cells, the number of characters varies in the cells so a left, right or mid is out. I want the formula to look to the cell and if any part of the text contains "ABC" then I want to return 1. =IF(C18="*ABC*",1,"") Cell C18 containts the letters ABC in order but my formula is still returning blanks?
The list of filenames in the "A-H" folder has different dates and codes, how do I go about fixing wildcard *Carnivore* so that regardless of the date and code, there will be a match.
list of files in A-H folder ------------------------ Date_120313 - Carnivore - Code_A3622 Date_030213 - Herbivore - Code_A2231 Date_051212 - Hybrid - Code_A4431
The use of * as a wildcard in excel can be extremely useful, however, can it be turned off? I work in the education sector and am currently number crunching GCSE results. When it comes to calculating A* grades versus A grades, B grades, etc, I cannot seem to do it because of the wildcard.
As far as I know, a wildcard can't be used in an "if" statement. I have a list of "job names" that vary slightly from time to time. When we use a Bunnings "(building account) people give a job name such as "121 William Drive". Sometimes however, the person in the store spells it incorrectly. Is there a way to find a wildcard in the following manner?
Find any word that begins with "121" and ends in "ve"
In a1 it says: "121 wilaim jnes drive" I have tried the following formula in b1:
=IF(a1="121*ve","yes","no")
Why does it say "no"? Is there a formula for using wildcards in an "if" statement?
I was wondering is there away where i can lookup A:A anything begging with Ves* ? and then i need to copy the formula down to pick up the next one in the list
im guessing something like =VLOOKUP(A:A,"Ves*") but i havent got a clue
I need to lookup data from four different columns to match a value in another sheet. The lookup value has to start with "4" in order for it to be the correct match. I've tried the following formula for just two columns:
The problem I have is that the wildcard function isn't working in my If statement (apparentley that's how Excel was designed). The first vlookup returns the value "40042565" but Excel doesn't show it matching the criteria with the wildcard ("4*").
Any way to lookup the value in C2, search through multiple columns, and return the value that starts with "4"?
We have an ISP based antispam system which can sometimes incorrectly pickup messages as Spam. I would like to scan through all quarantined messages every week to check that this does not happen. I can export a report from the program to excel but what I would like to do is remove subject lines with common spam words (Viagra, Russian etc) so I don’t need to check these, this would in effect reduce the report by 85%.
Ideally the ‘spam word’ list would be on the second sheet as in example and could be added to as I find obvious words.
The ideal end result would either be all lines which have a word from the ‘Spamwords’ sheet are removed or all lines which do not match the words in the spam list are copied to a new sheet.
I've this question, how do i extract a part of the text using wildcard(example "ID=") in the string in the whole columns and copy to other columns or delete the unwanted.
Below is a example which i create for better understanding.
I'm trying to use a "WildCard" in an Array Formula to both simplify and handle variations in several formulas.
My work sheet is used for Time Cards. I have a tab for each of the 52 weeks in the year and each tab is labeled "W1", "W2" for Week 1, Week 2, etc.
On my SUMMARY tab, I'm using an array formula that looks at cell "'SUMMARY'!D6" for a project number, and than searches through all of the Week Tabs for anyone that has charged time against that project.
The employee would enter the project number in D12:D35 of a given weeks tab with each row representing a different task for that week. In some cases they may have several rows for the same project number.
On the SUMMARY tab, the formula for Week 1 is in cell E6. For Week 2 it's in F6, etc.
I was thinking I could simply place the sheet tab name in the row above (i.e. D5, E5, F5, etc., and then write the formula for 1 cell and copy it to all of the other cells on that row.
Currently my formula looks like: {=IF($D$6="","",SUM(IF('W1'!$D$12:$D35=$D6,('W1'!$N$12:$N$35))))}
I was thinking the formula might look like: {=IF($D$6="","",SUM(IF('&E5&'!$D$12:$D35=$D6,('&E5&'!$N$12:$N$35))))}
But that isn't working.
Additionally I was hoping to do something similar on the SUMMARY tab by referencing a different spreadsheet with something like: ='[&A1&]Sheet1'!$B$51 Where cell A1 has the value "Time Sheets - 01-17-09.xls"Currently the formula looks like: ='[Time Sheets - 01-17-09.xls]Sheet1'!$B$51
I've been looking around for a way to use the countif function in excel with a wildcard, I have a excel list with a column containing data which looks like: ....
