I'm trying to identify if text contained in one cell is also contained in another cell. I used the following formula with some success, but it is not successful in all situations, and I don't know why.
=IF(ISNUMBER(SEARCH(+A1,C1)),"yes","")
Attached is a spreadsheet with the example.
I have 2 tabs in a 2013 workbook. Inventory Receipts and lookups. One of the Data verification lookups I have is a drop down list in each cell in Column B (eg: Cat, Dog, Mouse) In the lookups tab I have another cell range containing the sounds (eg: Meow, Bark, Squeak).
What I am trying to acheive is, if B2 contains Cat, then return Meow in B3, if B2 contains Dog then return bark in b3 and if B2 contains Mouse return squeak in B3. Ideally I would like the formula to return the text from my lookup sheet (eg: 'Lookups'!C2,'Lookups'!C3,'Lookups'!C4). So depending on which option they choose from my animal drop down list .. the correct noise would automatically fill in.
I have a sheet in which some of the cells have two strings separated by a linefeed. I have come up with a cumbersome formula which will let me check if either of the two strings is a member of a list stored on another sheet. However, it fails if there is only one string in the cell, presumably as there is no linefeed for the formula to find. How can I modify the formula to cope with this situation?
There are also on occasions, three strings in the cell, but I can't seem to access the middle string with the formula. Simplified spreadsheet attached to show the problem. This must be formula-based, as we have a no VBA policy. If you think there is better way of doing this, please let me know.
For all bank transactions I've downloaded, I would like to add a subject. The list contains many hundreds of rows, so I tried to make a macro for it. Unfortunately I cannot manage to search if a cell contains a certain text string. I came up to:
View 14 Replies View RelatedI'm having a hard time figuring out a solution for this situation. By the way I'm working in VBA macros. I need to check a cell for two things: if its empty or if it has something besides numbers in it. I know how to code the rest of what I need, but I'm just stuck on identifying if the cell has something else besides numbers inside it.
Possible entries would be "Isn't an enhancement" or "NOT A SIR", but I can't be sure those are the only things so I just generally need to check if there are any letters in that cell or not. One thing to note is that the contents of the cells are in text format and not number (the cell has a little green arrow in the top left that tells me about it). Any help would be appreciated, thanks in advance! Oh, just remembered, the number has to be 5 numbers long so I thought of using the Len() function but that doesn't cover things that are words under 5 letters like "None".
I'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G) I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong.
Sub RatingsFix1SP()
Dim FindWhat, rngCell As Range, i As Integer
FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+")
For i = 0 To 3
For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp))
If InStr(rngCell, FindWhat(i)) <> 0 Then
rngCell.Offset(0, 6) = 0.15
End If
Next rngCell
Next i
End Sub
To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%. It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.
a1=aaa
a2=111
a3=222
a4=333
a5=bbb
a6=444
a7=555
a8=ccc
a9=909
a10=234
if a1 = aaa then copy a1 to b1 until the cell =bbb
if a5= bbb then copy a5 to c1 until the cell =ccc
if a8= ccc then copy a8 to d1 until the cell = ddd.........
I am trying to categorize a list of words based on the first letter.
For example:
[Code] ......
I want to see if the letter 'D' in 'Data' occurs in a range from A-G, if so display "A-G" in cell B,
else if the first letter occurs from H-M, display H-N in cell B
else if the first letter occurs from N-R, display N-R in cell B
else display S-Z in cell B.
is there a formula which can locate instances of same text even though due to spaces it may look different? for example, "bad apple" and "badapple" are essentially the same.
View 2 Replies View RelatedI have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
I have 3 columns, C1, C2, C3. I have to return a value for the following conditions
1. Check for the presence of a particular text in the C 1 (duplicate values can be present)
2. Check for the maximum value in C2, if the second for the particular text from the first column (duplicate values can be present)
3. If the text is found, I need to return the Maximum value from column 3. Else I need to return 0.
My formula has been returning zero despite the fact that I have a larger valid value in column C3.
The formula(s) I have been running is
1. IF((AND(C1 = "E", Max(C2))), (C3),0)
2. IF((AND(C1 = "E", Max(C2))),MAX (C3),0) (In case of duplicated in C3)
3. IF((AND(C1 = "E", Max(C2))),MAXA(C3),0)
Can the below be achieved with any formulae or macro -
I have 2 worksheets (sheet1 & sheet2). In sheet2, i have some colors in column A (e.g. "red" in A1, "blue" in A2, "green" in A3.
In sheet1, i have some text strings in column A, e.g.
A1: I love red color
A2: my pen in blue
A3: Green grass
A4: Orange juice
A5: I like red and blue
What i want in corresponding cells in column B, is the name of the color which is in column A. so my output should be
B1: red
B2: blue
B3: green
B4: "NIL" (any error message)
B5: red (even though it contains 2 color, i am happy to show any one of the colors it contains)
I need to check if the value in cell A1 contains a certain text, I am using IF(A1="*test*","Y","N") but it does not seem to do the job.
View 2 Replies View Relatedis there a formula that i can use to check if a text in a cell is in bold?
i.e something like :
=isblock(A2)
to return true if the text in A2 is in bold
I want to write a formular to initiate an action if a particular text is not in a list For example, I have a list in column D1:D50, and I want the following to happen in say cell A1
If "apple" is not in list D1:D50, then in put "N" in cell A1, else input "Y".
am trying to write code to check if a cell contains specific text.
Basically, I hide all of the rows on the spreadsheet, and unhide them only if certain conditions are true.
Everything else, including first check is not a problem, it is just the part where I need to find specific text within a cell that contains various words.
Also, the cell I am searching for the text in is on a different sheet within the same book.
e.g.
------------------------
'Check for Specific parts
If Cells(intCount, 11).Value = "Yes" And ??(Does Cell 'x' (on a different worksheet) Contain the Word "Specific")? Then
'Select row
Rows(intCount).Select
'hide row
Selection.EntireRow.Hidden = False
End If
------------------------
I have a textbox in a form and i need to check if the text posted there begins with zero or if it contains spaces. Ex:
05E 9050 01
if this is the text then it shoud warn me becouse there is a zero in the begining and it contain spaces
4P 565001
if this is the text then it shoud warn me becouse there is a space
I'm on a time crunch and can't remember or seem to be able to find how to check if a value stored in a variable is a number or is text.
I want to be able to define QTY under the general public definition with no designation (i.e. integer, date, string...so on.)
The variable QTY could be anything and I grab some parsed data from a cell, if it is filled with a number I want to keep it, if not I want to delete it.
I know how to keep it or delete it, I just need to know how to check for integers...(check to see if it is a number from the keypad...I can still store it as text -- maybe integer isn't the best word).
I see you (whomever you is) fixed the "Deletked" in the message box...it is now Deleted. I can't give you a hard time about not following the rules now.
in my work sheet I need to Hide & Unhide some text box By checking a checkbox it means if user mark the check box the text box will be appear & if uncheck it the text box will be hidden
View 8 Replies View RelatedWhat I am attempting to do is search if 2 cells contain specific text. For example in Column C I want to output "Yes" only if cell A and B are Yes otherwise the output is "No".
ABC
1YesYesYes
2YesNoNo
3NoNoNo
I have tried =IF(A1=B1="Yes", "Yes, "No") to no avail
I am trying to generate a sub that read the name of a check box (which is in text format) in a spreadsheet in this case sheet13 and according to that ask if that check box which is in another sheet (sheet4) is true or false, I mean if it checked or not, then perform other actions
But I am getting this following error
Run time error ‘438’
Object doesn’t support this property or method
I tried several things but always get an error.
Does some body know how to make the checkboxname variable a valid name for the checkbox object in sheet4?
i have number of cells as below and i need to make macro to check last character in each cells if text the result will be 0 and if number the result will be 1
A1 42SRY239Acd2
I've two lists of text - one long with multiple words and one short with one or two words. (sample data: [URL]
I would like to check if List A has the word(s) present in List B, and return a Y/N accordingly.
I am using a check box on a form i have created. If it is unchecked (default = checked) then it enables a text box (default enable = false). The problem is if the user changes their mind and recheckes the check box it will not disable the the textbox. The text box stays enabled no matter what the check box's status is. I am unsure how to approuch making the following happen:
checkbox = True ----- textbox = (enabled = False)
Checkbox = False ------- textbox = (enabled = True)
i need some formula to data validation with this criteria :
allow only text "DROP" and "check" ...
What I want is to find out whether a value in a node can be found from a group of other nodes. I've tried lupike this, but it doesn't seem to work properly.
=IF(ISNUMBER(SEARCH(C1;$A$2:$A$200));"WAS FOUND"; "NOT FOUND")
I guess one solution could have been using concatenate function, but why on earth can't I use the function like this: concatenate(A1:A5)? Why do I have to put each term individually?
1. I have text message on Excel Sheet 1. I have designed a form that has Multiple Check Boxes. Primary, CO-applicant and Co-signer vertically placed. And I have 3 Check Boxes that have been placed Horizontally Alerts, Public records and Mismatch Information.
What I am looking is I should be able to Pick 1 or all the 3 from Vertical Check Box which would be Primary, or co-applicant and Co-signer and also appropriately pick any from the Horizontal Check Boxes Alerts, Public Records and Mismatch Info.
For example: I the Text Box1 below should appear like this:
Primary: DOB Mismatch or and Bankruptcy any of those options.
Similarly for Co-applicant and Co-signer.
2. I have a Text Box2 on Comments page. The information from Text Box 1 Appears in Text Box 2. Once the form is run I am unable to make corrections if any in Text Box 2. Is it possible for me to edit the test that is copied in Text Box 2.
I receive thousands of raw data every week as shown in the excel sheet {raw data}
I would like to use a macro that could automatically sort the raw data like the format in the excel sheet {template}
For example:
Looking at the raw data and compare with the template, i would have to delete the whole columns that have (BU_CODE , EAN_CODE, SUPPLIER_CODE, SUPPLIER_NAME , BRAND_NAME, and SUB_CATEGORY_CODE) and add in columns and name it as Principal, Cateogry , Brand and range).
Qtn.1 - Do i use 'Conditional formating' under 'Format' to change colour of Check-box text when 'checked'
I.e when ticked >> change colour of text to red
Qtn2 - If yes-, what's the syntax to put in 'Conditional formating' ?
I have a user form with 10 textboxes on and when you press the command button, VBA will send the values in each text box to a cell on the sheet. If a user only wants to enter one value, it zeroes all the others in that row!! Does anyone have any ideas please in very very basic lamens code!! It is only relating to the user form called "Figures1". I had an if command running in the "decweek1" modules, but it makes my procedure too long to do the same with the other one!
If you look at my code, you can see how unadvanced I am with this so really simple code would be gratefully received!! If anyone feels nice enough you could always write the code for this problem for me and send me back the workbook, however I don't expect anyone to do this!
It is too large to attach so it is here:-
[url]