IF Statements Based On Text In A String
Dec 12, 2008
I want to return Y or N for each row where a certain word (lets say "Apple") appears anywhere in the reference cell. Therefore in the below example the first 3 rows contain apple (somewhere in that cell) so Y is returned. I can get it to work when apple isd the only thing in the cell but not if it appears with text before and/or after it.
ie:
A B
apple Y
apple orange Y
orange apple Y
pear N
grape N
View 4 Replies
ADVERTISEMENT
Jun 29, 2014
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
View 7 Replies
View Related
Jul 23, 2014
I have been trying to work out a formula without success so far. If you look at the this text string
Code:
Depart Doncaster Depot with a Mini bus at 0649
The "Mini" word in red is the one that I am trying to work out with.
Here is the problem. The "Mini" word could be replaced with 7 other words - Walk, Artic, Dart Smart, Maxi, Smart, Staff Car, or just bus with no explanation as to what type it is.
I need a formula to look at the text string and work out which one of the above categories are included in the text and based on that gives me either W, A, Dart Smart, Mx, S, Car, and the last to be just N.
View 9 Replies
View Related
Nov 20, 2009
I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler
The Data Begins in Row 5 of Column A
My Current Macro is as below which I have adapted from another one I used
It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.
View 6 Replies
View Related
Mar 16, 2008
I have a sheet named: tool
In this sheet I have a long list of data that i would like to have sorted out on different worksheets that needs to be created by the macro. The deciding factor for the sorting are found colum B. In this colum there are different text strings. I would like for the macro to look at this text string and copy the entire line over to the respective work sheet. the work sheet needs to be named according to the text string in colom b.
Furthermore in the sheet named tool the first 3 lines are headings and i would like all of these transfered to each of the new sheets.
View 9 Replies
View Related
May 5, 2007
I am wanting to find within a cell the following pattern "ANNNN" - "A" meaning any alpha character A-Z and "N" meaning any numeric 0-9. The pattern ANNNN could be anywhere within the text. If the cell contains text with that pattern the text would be deposited in the adjacent cell. Lorem ipsum dolor sit A1234 amet, consectetuer adipiscing elit. Mauris tristique. Cum sociis
View 5 Replies
View Related
Oct 11, 2011
I need to change the colour of a cell using VBA based on the input of a certain word. I have used teh below code but it doesn't pick up different variations of the word
Dim cell As Range
For Each cell In Range("E2:E500")
If cell.Value = "Check" Then[code]....
This code works fine (i have put it into worksheet change) when I use "Check and Mark and Chase" but some users are bound not to use capitalised first letters and on these occasions the cell colours are not changing.
How to add something to thie code above to make it work for any variation of capitalisation?
View 2 Replies
View Related
Oct 13, 2009
is there a function that will split a text string based on a symbol. I know how tyo use left and right, which are based on a set number of characters but I want to split based on a "/" mark. whats to the left of the "/" mark and whats to the right of the "/" mark. any ideas. an exaplme is: tom / tim. i want a formula that will put the word "tom" in a cell and another formula that will put "tim" in another cell.
View 9 Replies
View Related
Apr 8, 2013
Here is an example of the data I get each day Letter order granting Sabine Pass Liquefaction, LLC's et al 4/16/12 request to add an alternate water source etc under CP11-72.Letter order granting Cameron LNG, LLC?s 4/5/13 filing of a request to introduce natural gas or process fluids into the BOG Liquefaction Project under CP12-15.Letter order accepting NorthWestern Corporation's 8/7/12 submittal of revisions to its transmission planning process to comply with the Commission's June 8, 2012 Order under ER11-2932.Letter order approving Public Service Company of New Mexico's 12/7/12 filing of a joint Offer of Settlement with Navopache Electric Cooperative, Inc under ER11-4534 et al. How can I set up my spreadsheet and what formulas can I use to search and return a value for each text string based on the attached table (column B)?
E3a7qta.png
View 1 Replies
View Related
Jul 5, 2014
A
B
1
Terry lives in England
=FORMULA
2
Claire lives in Spain
3
Paul live in France
I'm looking for a simple formula within the cells of column B to populate the cell with data based on the "country" mentioned in the string of cells in column A. There will be 20 countries and dependant on the country mentioned a 3 character country code will be entered in column B allowing for easy filtering of people based on country code. so something along the lines of if A1 contains "England" B1 equals ENG etc (for abot 20 countries)
The output should look like below
A
B
1
Terry lives in England
ENG
2
Claire lives in Spain
ESP
3
Paul live in France
FRA
View 8 Replies
View Related
Jun 13, 2013
Working with a scheduling report and trying ultimately to get it down to a line item report with the associate information and their earliest start and latest end for the week. Already made a lot of fixes to the formatting involving junking empty cells and trash data, but running into a road block on some of the trash data.
Here is the format of the report at this point. Names have been changed to protect the innocent.
Agent: 2366 Bacon, Kevin
Date
Start
End
[Code].....
So what I need is something that will iterate through the report...such as an while and find the cells in column A that start with "Agent:" It needs to compare that cell to a stored value to see if this "Agent:" is a repeat. If it is then it needs to delete that row and the next one and shift up. If it is not then it overwrites this "Agent:" with the previous one in the stored value and continues the while.
I know what I want to do...but not shure on the code cause I am not great at VBA.
Oh...if it matters probably need to allow for about 10k line items to iterate through...right now report is at 6k...but allow room to grow.
View 6 Replies
View Related
May 8, 2009
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
View 9 Replies
View Related
Jun 18, 2014
I'm trying to set up a dashboard with a table and some formulas so that I can just copy a data export into one tab and have the table perform various Vlookups, charts, etc. The trouble I'm having is that some of my cells contain a mix of text, and I'm looking for specific text only. Example:
Address |Tags | Result
Email 1 |t1 t2 t3 t4 | Email 1
Email 2 |t1
Email 3 |t2 impact1 | Email 3
Email 4 |impact2
Email 5 |impact2 t2| Email 5
My data source has thousands of rows, and I'm looking at the Tags column. I want to use a Vlookup that gives me the email when [Tags] contains "t2", but my search is getting confused with "impact2". Is there a formula to search for "t2", but not get confused with "impact2"? I considered using =NOT, but I don't want to exclude cells just because they contain "impact2". If there was a formula for =IGNORE, that would be marvelous. My formulas are getting longer & longer, but they always seem to break in the end.
Basically I'm trying something like:
=IF(OR(ISNUMBER(SEARCH("* t2*",Table1[Tags])),SEARCH("t2*",Table1[Tags])),"Yes","No")
(Once I get the IF figured out, I'll use Vlookup to bring out the Address).
View 1 Replies
View Related
Dec 29, 2011
I have two sets of data and am applying an IF statement to each one. What I would like is if the answer is positive, to have the value displayed. If the answer is negative to have a string of text displayed.
They way I have it currently set up is IF statement #1 performs the logical test. If the result is a positive number, it performs a calculation. If the answer is negative it returns a string of text.
IF statement number #2 performs a logical test and if the result is positive it performs a calculation. If the result is negative it returns a 0.
Now, this setup allows me to add the two IF statements together if the results of the logical tests are both positive or if the #1 is positive and #2 is negative. However, I get a #VALUE error is the results are both negative as statement #1 is returning a string of text.
How can I set this up so I can get the string of text as the result if the results of both logical tests are negative?
View 2 Replies
View Related
Aug 14, 2014
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Example;
Cell A1 has 123XVF1234
Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
Example;
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
View 7 Replies
View Related
Sep 18, 2012
I have 2 option buttons linked to cell A1 and I want to run a different macro based on what A1 shows. The code I've been trying is:
Code:
If A1 = 2 Then
Application.run "ship1"
ElseIf A1 = 1 Then
Application.run "ship2"
End If
Using this runs neither of the macros though. I've tried a few different things and still no luck.
View 5 Replies
View Related
Jun 9, 2013
I have a Workbook which I'm trying to apply the following VBA to (the moment Sheet11 is opened):
If Sheets("Sheet11").Range("B8:B372")=Sheets("Sheet8").Range("F1") - Dates
Sheets("Sheet8").Range("L24").Copy - Numbers
Sheets("Sheet11").Range("B8:B372").PasteSpecial PasteValues.Offset(0,1) (paste into relevant cell in Column C)
Else, 0
I would then like the relevant cell in Column D to be activated. IE: Offset(0,2) so the user can then enter their relevant data - more numbers.
View 9 Replies
View Related
Jun 9, 2014
In column A I have the debt name i.e. the company that owes us money.
In column B I state the amount owed to us
In column C the month by which the income is due. This is selected from a drop down list: M1, M2, M3 etc up to M12
In columns D through O, I have the forecast of what payments will be recieved in months 1-12
In the next cell I want to total payment that will be recieved by the due date. So, if the the cell in Coumn C says M5, then it would just add up the amount forecast in months 1-5, but if the first cell equals M2, then it would add up the forecats for M1 and M2. What formula do I put in here?
e.g. Manually enteres numbers would look like this, but I need a formula for cells in column P
A
B
C
D
E
F
G
[Code] .......
The only way I can think of doing this is with nested IFs but the limit is 7 and I need 12. I have used 12 monhts here for example. In fact on the real version I have 48 months and obviously can;t do 48 nested IFs.
View 6 Replies
View Related
Aug 12, 2008
i've got a bunch of vlookup statements in my worksheet and i now need to create if statements based on them. however, the if statements do not recognize the vlookuped values. how can i get the if statements to work based on what the cell SHOWS and not what the formula is? i realize i can copy and paste values but i would like to retain this worksheet as a template.
View 9 Replies
View Related
Oct 6, 2006
I’m trying desperately to get this if statement to work
If Workbooks("Master.xls"). Sheets("intro"). Range("A2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("B2") = "" Then
For Each vaFileName In .FoundFiles
ProcessDataCrit1 vaFileName
Next
If Workbooks("Master.xls").Sheets("intro").Range("A2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("B2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("C2") = "" Then
For Each vaFileName In .FoundFiles
ProcessDataCrit2 vaFileName
Next
If Workbooks("Master.xls").Sheets("intro").Range("A2") <> "" And _
Workbooks("Master.xls").Sheets("intro").Range("B2") <> "" And _ ..................................
Basically what it should do is to start the code ProcessDataCrit1 if there is a value in A2 but not in B2 or C2, start ProcessDataCrit2 if there is a value in A2 and B2 but not in C2 and start ProcessDataCrit3 if there is a value in all three cells. The formula is working for ProcessDataCrit1 as soon as there is a value in B2 the formula is not working.
View 2 Replies
View Related
Jul 26, 2006
I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups.
Example: ....
View 14 Replies
View Related
Mar 14, 2012
I have a workbook with two sheets 'Driver details' and 'Jan Raw Data'.
If column B Employee (Jan raw Data) matches the names in Columns B&C (Cloumn B & C, Sname, Fname) on the 'Driver details'.
If it equals a certain name i.e. Jon smith, then in empty column E of the 'Jan raw data' then copy the entire row of that individual from the 'driver details'.
Driver details
A B C D E F
Emp No Sname Fname Desc Desc Reg
123 smith Jon opticar BVA MA2
456 pratt mike opticar TMA YP7
Jan Raw Data
A B C D
Occup Emp Product Supp no.
Crawley Smith Jon unleaded 3453534
Crawley prat mike unleaded 89797
View 3 Replies
View Related
Jan 31, 2008
Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.
View 3 Replies
View Related
Nov 22, 2008
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
View 2 Replies
View Related
May 26, 2008
Sheet one will contain the following:
DATE A B C D E
2/22/2008TRUEFALSEFALSEFALSERon
2/23/2008FALSETRUETRUETRUEPhill
2/24/2008FALSETRUEFALSEFALSETracy
2/25/2008FALSEFALSEFALSEFALSESharon
2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
EXAMPLE:
2/23/2008Phill
2/26/2008 Bill
View 4 Replies
View Related
May 21, 2014
Im trying to pull together a formula to achieve the below. What i have is a spreadsheet to be used by staff in different countries so im looking to make it easy to modify the categories used without the user needing to do anything complex with formulas.
The right hand side of my file (C:E) shows a code for each year we have covered a country with the year as a prefix (2014 - Blah). The left (A:B) is a summary showing the latest year we covered that country.
Current formula:
{=IF(COUNTA(C2:D2)<=0,"","✔-"&MAX(LEFT(C2:D2&"0",4)*1))}
The current formula works fine if you set the columns to where the data actually is but for ease of use i need it to look at the data in rows 1:2 and match up the Continent/Country and then MAX the dates.
View 1 Replies
View Related
Jun 11, 2014
I need a VBA code to create a Text file which should be in the form of DML (my scenario is to get Insert Statements for the Data available in the Excel) from Excel Sheet. And this has to be done by assigning a macro to command button where by clicking on it the DML text file should be generated. Even the syntax for the scenario will do.
View 1 Replies
View Related
Oct 14, 2009
How can I combine the two below statements like these to make one "Or Statement"? Sorry...I haven't done excel and/or's in forever?
=IF(('Proj Info'!L10="main")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
OR
=IF(('Proj Info'!L10="serv")*AND('Proj Info'!L9="CT"),"BMSVC",'Proj Info'!L9)
View 2 Replies
View Related
Jul 9, 2014
I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.
The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].
The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?
View 3 Replies
View Related
Mar 28, 2009
refer to the attached workbook for reference. I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.
View 3 Replies
View Related
