IF Statements To Find Certain Text And Exclude Certain Text?
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
ADVERTISEMENT
Feb 3, 2014
Is there a way to do a Formula (SUM) but exclude cells with invalid values? I would like to add up all values in attached spreadsheet for cells A14 to A28 and again for A49 to A63, as well as get an average C-14 to 28 and again for C49-63.
As some of the cells contain text rather than numbers, the formula doesn't work. how to exclude these cells?
View 4 Replies
View Related
Apr 19, 2014
I'm trying to sum up the total of a list excluding a list like this:
Column B
012*
019*
1758*
70228
70229
70230
70241
In my worksheet 1, it contains e.g.
Column AC
012-something
012-text
019-jumbo
016-things
70228
70221
Column W contains the numbers I want to add up:
$15
$12
$10
$19
$15
$11
My current formula is:
=SUMPRODUCT(--ISNA(MATCH(Sheet1!AC$4:$AC$157,B2:B7,0)),Sheet1!W4:W157)
It has managed to exclude the numbers (e.g. 70228, 70229, etc) but not the text strings (e.g. 012*) when summing it up
View 2 Replies
View Related
Jul 14, 2014
having the formula exclude counting text in cells.
=IFERROR(SUMPRODUCT(--(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!A4:A44")=Tracking!$A3)*(INDIRECT("'"&TEXT(B$2,"mm-dd-yy")&"'!D4:J44")>8)),"")
I would like it to only count numeric values over 8.0. Currently if I have text in a cell such as "PTO" it returns as a added value.
View 3 Replies
View Related
Apr 1, 2009
I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.
View 7 Replies
View Related
Nov 6, 2012
I have a speadsheet where I have a cell that reads a named range via OFFSET. The range consists mainly of numbers but a few cells contain the text UL (Unlimited)
The cell that "reads" the OFFSET is then used in another cell where it is multiplied by either 2 or 3 depending on user input.
The problem occurs where the cell attempts to multiply the text "UL" which results in "#VALUE!"
How can I have this cell with the multiplication bypass the multiplication and simply return the text "UL" in the case of reading text from the named range.
View 2 Replies
View Related
Jan 12, 2010
I've setup a macro button to sort some stockpile items from A-Z so entering quantity on hand can be done more easily. Each cell is a drop-down selection so at various times, there can be different stockpile items in the list, thus why, I setup a macro to quickly sort the list.
However, I've got some text (Select Stockpile Item) that I wish not to be included in the A-Z sort as this might put items starting with T or later at the end of the sort.
Sub SortMySpecialStockpileA2Z()
If MsgBox("Are you sure?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
Else
Range("K4:L110").Select
ActiveWorkbook.Worksheets("MyStockpile").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MyStockpile").Sort.SortFields.Add Key:=Range("K4") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("MyStockpile").Sort
.SetRange Range("K4:L110")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End If
End Sub
View 9 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
Jan 7, 2014
I have an existing Cash Flow Report that has a column of abbreviated/shortened (WBS Element) title where each cell contains a unique three lettered/numbered amount of characters (Example: 200). These three abbreviated character cells are specific and relate to their full/longer (SAP WBS Element) title (Example: WBS DWRRI-BW066-200).
In my attached excel model (Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx), I need a formula for the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11 that will look at the abbreviated three lettered/numbered (WBS Element) titles in cells C3 thru C11, then search and recognize its unique counterpart contained in the, "SAP WBS Elements Export" sheet and return this full/longer (SAP WBS Element) title to the, "Cash Flow Report WBS Elements" sheet in cells B3 thru. B11, just to the left of its abbreviated/shortened (WBS Element) title.
View 3 Replies
View Related
Jan 7, 2014
I have an existing Cash Flow Report for my work that has a column of abbreviated/shortened (WBS Element) titles where each cell contains unique three lettered/numbered characters (Example: 200). These three abbreviated character cells are specific and relate to their full/longer (SAP WBS Elements) titles (Example: WBS DWRRI-BW066-200).
In my attached excel model (Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx), I need a formula for the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11 that will look at the abbreviated three lettered/numbered (WBS Element) titles in cells C3 thru C11, then search and recognize its unique counterpart contained in the, "SAP WBS Elements Export" sheet and return this full/longer (SAP WBA Element) title to the, "Cash Flow Report WBS Elements" sheet in cells B3 thru B11, just to the left of its abbreviated/shortened (WBS Element) title.
Find, Locate, and Align WBS Elements to Cash Flow Report.xlsx
View 1 Replies
View Related
Aug 18, 2013
I am trying to find specific text throughout an excel document (2007) and replace it with the same text but in italics. I tried using the options/format function and selecting italics for the "replace" text but it replaces the entire cell text in italics instead.
View 5 Replies
View Related
Mar 8, 2013
How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.
A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.
After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.
New words will be added to the sets of words continually as required.
Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.
I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.
The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.
If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.
Happy to consider a different solution if the task can be done better a different way.
Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.
View 1 Replies
View Related
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
View Related
Mar 4, 2014
I have a worksheet with several columns. I need a formula to search column D only and each time a specific location is identified to replace that location with alternate text. Example (ORIGNAL TEXT):
Column Dtext to text help.xlsx
BIRD
FISH
DOG
BAT
BUG
I need to search that listing and each time the word BIRD is mentioned have it replaced with FEATHERS and each time DOG is listed have it replaced with TAILS Final result would look like:
FEATHERS
FISH
TAILS
BAT
BUG
All other text should stay the same and replacement text should appear in the cell of the text it is replacing. This is a sheet used by multiple people several times a day and so the Find/Replace option really won't work.
Have working on this for at least 6 months and it just isn't going to happen for me. I thought I could use a Conditional format, but that is producing no results either.
View 3 Replies
View Related
Apr 22, 2006
I am using the following code.
Function VLOOKUPRow()
Dim SV As Object
Worksheets("Midterm").Select
SV = "UserFormAppend.TextBoxInvID.Text"
VRow = Columns(1). Find(What:="SV", After:= Cells(1, 1), _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True).Row
MsgBox (VRow)
End Function
I am trying to give the VRow variable the value or the row that The text from my text box is on. How do you make the what look for a variable. I can get it to work if i put a constent in the what. how to make the what look for a variable. I'm new at this forum thing so im not quite sure how to check for answers.
View 2 Replies
View Related
Apr 10, 2008
I want to search for a word in column A and when I find it I want to copy it to column B.
Column A is a description that can be 6 or 7 words long. Column B is a single word.
Example:
Col A
Engine Kit, V-8, 306, forged.
I need to copy the word Kit to column B.
View 9 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
Dec 7, 2012
I have a large .txt file that I would like to edit using VBA. What I would like to do is to find each of the following text strings in the text file :
De:
De :
Envoye
A:
Objet:
If the character right before those text strings is not a carriage return (chr(13) then add a carriage return.
I would like the code to read each line in the text file and execute the task mentioned above.
View 9 Replies
View Related
Oct 15, 2008
I have a couple of excelfiles in which someone made a flowchart with text boxes. I would like a search code that would help me find a text or number in those text boxes. The CTRL+F function only works on cells. Does something similar exist for text boxes? Or can this be made in a macro?
View 9 Replies
View Related
Dec 9, 2009
The macro recorder doesn't let me record a macro that finds text box #9, go to the end of the existing text in the text box and then add my text.
When I record the macro and go to the box, it doesn't appear in the code (as well as not working).
View 9 Replies
View Related
Aug 27, 2006
I'm trying to create an Excel function that allows me to choose a row from a table based on text found within entered data. For example, say I have a table called "categories" that looks like this:
Categories
cat
dog
mouse
Now in a different section of the workbook, I want to be able to enter an arbitrary number of sentences, one per row, that look something like:
"It's raining cats."
"The mouse is hiding."....................
View 3 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 9, 2007
I have spreadsheet A with 14 columns of data ie Col A to N, in Column I there are file names. Somewhere within the file names is the client code. The file names do not conform to a standard naming convention so the client code is not always in the same position or the same number of characters long. I have a separate spreadsheet (spreadsheet B) that has the list of client codes in column A and the associated business unit code in column B.
I want to be able to find the client code within the file name of spreadsheet A and then add the associated business unit code to column O
View 11 Replies
View Related
Aug 5, 2013
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies
View Related
May 23, 2006
I am having trouble getting my IF statement to test if the cell contains the text "sale" return "X" if not "Y". I need it to search through the text string in that cell and find a certain word, and if it finds that word, retrn a value. I am really having difficulty with is what symbol or function do I use for the logical test? (i.e. =, <>, MATCH, INDEX?)
View 4 Replies
View Related
Oct 8, 2006
Attempting to hide columns (of cities) via VBA generates an error when that same city is reselected (either individually, or as part of the group) in the list box, upon clicking the 'Hide' button.
The error happens at this point: ...
View 3 Replies
View Related
Oct 14, 2008
1. Search an excel sheet "column" for a particular type of text and insert values based on that text (if found) in another column.
e.g I have column A1 to A10 with different types of text. I would like to search for the keyword "Risk is high" OR "Risk=High" for each cell in the column and insert a "1" if found beside it's corresponding "B" column. If not found, I would like to insert a "0".
So, if the text "Risk is high" OR "Risk=High" was only found in A6, I would like B6 to be "1". Rest of the values in the B column would be "0's", since the text was not found in any of the other cells.
View 10 Replies
View Related
Nov 21, 2007
I have a macro which imports data from a mainframe dump text file and performs 'Text to Columns' on the imported data so that formula in the spreadsheet can act on the data. The code works perfectly well when I use it, but if a different user logs on and performs exactly the same mainframe dump and import macro the Text to Columns action splits the raw data in a different way and the result is that the split renders the formulae useless.
I've experimented a little and for some reason it appears that the 'Field Info' parameters which are produced when the Text to Columns function is recorded in a macro differ between users even though the raw data is exactly the same.
FieldInfo:= _
Array(Array(0, 1), Array(18, 1), Array(35, 1), Array(56, 1), Array(70, 1), Array(88, 1), _
Array(102, 1))
View 6 Replies
View Related
May 16, 2014
My aim is to find the end working date for each task, as well as the next working date for the next task. The working days for this employee are only Monday (6hours), Wednesday (6 hours) and Friday (8hours).
D
E
F
G
H
Start Date
Duration
Completed Days
Remaining Days
End Date
4
5/05/14
3
=IF(TODAY()>=H4, E4,IF(TODAY()
View 3 Replies
View Related
