Free Microsoft Excel 2013 Quick Reference

Count if cell contains any of the text/number in red font

Hi All,

I want a vba code to count if a range of cells have any of the text with red font. I have seen so many posts regarding counting if a cell contains a red font. But for eg. Cell A1 has a text "abc d" with "ab" in black and "c d"in red, so it should also count as 1.

Ranjit.


Hi All,

I want a vba code to count if a range of cells have any of the text with red font. I have seen so many posts regarding counting if a cell contains a red font. But for eg. Cell A1 has a text "abcd" with ab in black and cd in red, so it should also count as 1.

Ranjit.

Basically, I would like the formula to accomplish the following:
If A3 contains any of the words from E11:E20 (aka, Table defined as "Groceries"), then display the contents from E10. If not, then "None".

Can this be done without VBA?

If not, I need help as I have always been able to accomplish anything that I needed to in Excel without VBA... until now.

Thanks! :-)

hello!

I want with one button in the toolbar to delete all rows that their first cell has any of the values of a list (I need to somewhere store a list of these values as well, to be easily accessible and updateable, but without appearing in every EXCEL file I open)

thanks!

I've got a table in the following format:

Date Price
01/01/07 n/a
02/01/07 n/a
03/01/07 10.00
04/01/07 11.00

I'd like to find either the value or the relative position of the first number in the list, i.e. in this case, 10.00 or 3 (because 10.00 is the first number after two text entries).

I tried =VLOOKUP(15,A2:A5,TRUE) and =MATCH(15,A2:A5,1) but neither formula works, giving me 11.00 and 4 instead of the desired 10.00 and 3.

Any tip would be great appreciated!

Thanks,

Daniel

Hi
Iam trying to find a formula that will search the beginning part of a cell, for a certain piece of text, and then counting the result if criteria in another column is true.

I have used the sumproduct formula before, but this only works if the cells specifically contain the text that is searched for.

=SUMPRODUCT(($B$2:$B$72="N/R")*($C$2:$C$72=1))

the data that I have has the "N/R" at the beginning of each cell eg Cell B8 = N/R BLACK DRAWING PAD or some of the text looks like this eg Cell B12 = (N/R)POSTER PAINT NEON

so what I want the formula to do, is search column B for any cells that have N/R or (N/R) at the beginning. then count if it matches certain data in column C

any help will be much appreciated

Hi guys

Probably an easy one for you lot but here goes.

I have 1000 rows of text containing different things (text). I want to count the cell if it contains a text string.

eg Cell contains "Microsoft Navision". I want to search to see if the cell contains "nav" (just the string as opposed to the whole product)

Can anyone help?

Thanks in advance
Stu

Hi guys

I have tried searching for a way for VBA to recognise in VBA if a cell contains any text, but most of my searches respond to specific cases for specific characters in a string

Basically, I'm just looking for the correct way to word a string of code that if it detects any text in column B (col 2 ) then it should not do anything, else it should cut the value in col 2 and paste it in col 8

'''''''''''''''''''''''''''''Check for values in column
B''''''''''''''''''''''''''
' For i = Range("A65536").End(xlUp).Row To 5 Step -1

If Cells(i, 2).Value   "<> TEXT? this is where I need help"  Then Cells(i, 2).Cut Destination:=Cells(i, 8)


Next

Similarly, this can be rethought as determining if the cell is an integer, ie if it is an integer (and not text) then cut from column 2 and paste to column 8

Thank You

Hello All!

Been reading forums here for a while, but could always find what I needed. Unfortunately I can't find the answer to this question...

I need to write an equation that checks a specified range of cells and if it finds the word "one" as part of the text string in column B, then it checks column C for a new text string, and if both conditions are true, it counts the number of cells where both conditions are satisfied and outputs that number. Further more, i want the next cell to make sure both conditions are satisfied and return the maximum value from column D...

I've attached a jpeg snip to show what I mean. Highlighted in RED are the answers that I want. Highlighted in YELLOW is where I got the answers.

Right now I can do part of what I want, that is I can check the "Fruit" column for the word "apple" using Countif(C5:C10,"apple") and further more I can return the max value using an array formula as follows {=MAX(IF("apple"=C5:C10,D5:D10,"FALSE"))}. My issue is adding criteria that searches column B for PART of the text, in this case "one".

This is merely an example, i actually have thousands of rows and columns... Any insight would be much appreciated! Thank you very much.

EDIT:

Am I getting close with this??:
{=MAX((D5:D10)*(B5:B10="one-12-do91")*(C5:C10="apple"))}
This works as an array formula, but instead of having "one-12-do91" in the second part, i want it to only have to satisfy the criteria of containing "one". I tried this {=MAX((D5:D10)*(B5:B10="*one*")*(C5:C10="apple"))} and it doesn't recognize the "*one*"... AHHHHHHHHHHHHHHHHHH

Hi all,

Hopefully a quick one here.

What is the formula in CF to get a cell to change colur if it contains less than a certain number of text characters.

regards

Damian

Hi all,

I have a work sheet with loads of urls in it like this one:
www.hot-tub-uk.co.uk/links/hot-tubs-&-spas.htm

I need a formula that will put an x in the cell next to it if the url
contains any of the following words:
link, links, resource, resources

Can any one help

Many thanks

Alex Kemsley

How can i change the color of a text in a cell automaticly if i write a text
in another cell.
Exampel: in cell C13 i have the text P2 in the color black. If ´the cell L25
is blank the text in cell C13 chould be black. If i type a text in cell L25
the text schould turn red.

Hi,

I have a column A of messages, and I need a formula for the adjacent column B that will put a 1 in the cell in B if the message in A contains any of the following words: xyz, abc, etc. and a 0 if it does not.

Any help appreciated. Thanks!

Basically, I want a formula that searches for more than one word anywhere within a cell, and if the cell contains any of those words somewhere in it then I want to display a value, but if the words aren't there, then stay blank.

For example, my cell could say: "Magenta, Rose, Lime, Lavendar".
I want to search this cell for any of the these words: "Lime", "Forest" or "Teal". If any of these appear, I then want the cell with this formula to say "Green".

I know the formula to search for only one word /phrase in a cell, I use: =(IF(ISNUMBER(SEARCH("yellow",A1)),"colour","-")), (i.e. if the word "yellow" appears anywhere in cell A1, then this cell will show the word "colour", if not then a "-".) I tried adding an 'OR' function in this to search multiple words but it didn't seem to work.

Thanks.

Hi,

Thank you very much in advance for your help.

I have attached a simplified version of the xls I am using. On the attached xls there are two tabs. The first tab (BUYS) has linked pictures of cells on the second tab (Warehousing).

I would like some code which would display the pictures only when the corresponding cells in Column F have a number in it. (This number could be anything)

e.g. If cell F4 has a number in it show Picture 1
If cells F4 and F5 have a number in them show Picture 1 and 2

My full xls (not attached) has another tab called SELLS. These two tabs (BUYS and SELLS) have 15 pictures each (1 for each row 4:18).

Ideally an exception would be written into the code so that if the xls was named "yymmdd Asian Executions" or "yymmdd Europe Executions" none of the pictures would show up.

I hope I have been clear but it's very difficult to be objective so please let me know if you need any more information.

Thanks again,

Emmy

Hi Everyone,

I'm trying to do something that I thought would be easy with an IF statement, but it doesn't seem to be working...I may need to write a macro to handle this....

Basically, I have three columns, one that has about 10 rows of numbers, the second that has about 400 rows of numbers, and the thirds is where the IF statement will reside. I need to be able to check if any of the numbers in the first column match any of the 400 numbers in the second column, and if they do, have it write some text in the third column. However, my IF statement doesn't like the fact that I'm using a range...any ideas?

-beveritt

How do I test if a cell has any of the special characters e.g. *,?,/
Thanks for any help.

I have a report that I run that I must print to a text printer and then copy and paste into an excel spreadsheet. I just started the job that requires this and the previous person in this position did a lot of work by hand that I believe can be automated with the help from this forum.

The data that is pasted into the spreadsheet is medications and comments listed with empty rows separating each entry.
I need to identify all the rows that contain the words “saline, glucose, PRN, albuterol, ipratropium, and insulin” and turn them red, then have them sorted to the top of the spreadsheet.

Each entry consists of several numbers followed by the primary sentence in the first row, this is the sentence that will contain the medications to be highlighted or turned to a different color. Then there are two to three sentences below this that are indented and will not be highlighted but need to be associated with the highlighted sentence in order to sort all the information together and move it to the top or bottom of the spreadsheet.

I have never used excel to do anything like this and while I am sure that there is information on the internet explaining how to do this, I have looked all weekend and am clearly not asking the question right. Thank you for any help you can give.

I have made a mock up of the report and uploaded it in the hopes that it will make my issue clearer. While the information is not all exact the way that it populates excel from a cut and paste from a text printer is very representative of the original format. The rows that I need to be conditionally formatted are any that contain any of the specified words and the comments beneath them. I hope this makes my question easier to understand.

In cell B1 I could have any one of the following text examples: big apple,
small apple, orange, apple, pear etc. In A1 I want a formula that will
return "apple" if B1 contains any of the text apple. ie if it read "big
apple" in B1 I want it to return "apple" in A1.

Thanks
GK

Help me please,

I can use most functions on excel. I'm looking for a way to write a formula that will use conditional formatting to shade the cell.

Entries could be:

H
S
O
T

These entries will be used in conjunction to the above entries

R
I
S

Thus I want to shade the cell based off of the R, I or S how ever they only appear as:

HR, HI, HS or
SR, SI, SS

That being stated I have tried to use a search function for the Text R, I and S. and shade the cell accordingly.

This would be used in an Array of B2:AF7 (six month calendar)

I am working on some genetic project, I have a column contain all the data with the gene name, which may come in a cluster in a cell, and i have a list which contain the gene i am interested, for example
Column B (gene list interested)
PCNA
GADD45G
bActin
p21

Column A (look up array)
PCNA;PCNA;p53
p53;p21
SOC2;SOC2
GADD45A

I want it return true if the string in column A contain any of the gene name in column B.
I can only come out a formula =isnumber(match(A2,B:B,0))>1 which returns only an exact match. what formula can i use to return if it contains any of the gene listed in column B?

Hi,

Hopefully I can explain this properly..

I have two tables: NAME & DETAILS

NAME just contains a list of names e.g.

Bob
Dave
Mike
Steve

then DETAILS contains a field which may contain these names, although this could be anywhere in the field, and some of the data won't contain any of the names from the original table e.g.

Bob Johnson
Mark Dave Roberts
Mike
Steven
Philip [Don't want]
Chris [Don't want]

What I want to do is produce a select query which will pull back all of the entries from the DETAILS table where it contains one of the names (i.e the first four entries in the above example.

I'm sure ths must be possible somehow but I can't for the life of me think of a way to do it.

Any help would be much appreciated.

Thanks,
Don

1. In column A i have a list from 1-12 (months in a year)
2. In column B i have the formula that references the corresponding cell in column A in the same row i.e. B1 "=(A1={1;3;5;7;8;10;12})"

Problem: This formula works only when the value in column A equals "1"...i need it to work when it equals any of the identified numbers in the set.

Any ideas???

Hi

I have a loop macro which currently opens a document, loops through a cell range on the "PPIIIFORM" sheet and first checks to see if the cell is empty, if its not then it returns the cell value into the column A in the Input_Reference_Table sheet. It then checks the cell colour and if its gray it returns false in column L column and if its yellow returns true in the N column on the Input_Reference_Table sheet.

Sub PP3InputRef()

Dim StrFldr As String
Dim PPWB As Workbook
Dim Nrow As Long
Dim cell As Range

Application.DisplayAlerts = False

StrFldr = ThisWorkbook.Path

Set PPWB = Workbooks.Open(StrFldr & "" & "HDE_PPIII_Input_Reference_Table_V1.xlsx")
PPWB.Sheets.Add.Name = ("Input_Reference_Table"): PPWB.Sheets.Add.Name = ("Values")

PPWB.Sheets("InputRefapd").Range("A1:Y1").Copy
Destination:=PPWB.Sheets("Input_Reference_Table").Range("A1:Y1")
 
Nrow = 2

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    If cell.Value <> "" Then Sheets("Input_Reference_Table").Cells(Nrow, 1).Value = cell.Value
    Sheets("Input_Reference_Table").Cells(Nrow, 12).Value = Not cell.Interior.Color = RGB(217, 217, 217)
    Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = cell.Interior.Color = RGB(255, 255, 0)
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell
However i need to add a line into the code above so that it does another check. It checks the column number of that cell and then returns the column number in the column K in the Input_Reference_Table sheet. However the number needs to start from 1 - 16 so that in the cell range F4:U644 if its F return 1, if its G it returns 2.....if its U it returns 16 etc.

However i am not sure how to do this. Can anyone help me?

Thank you

Jeskit

how do i receive the address of the cell of the largest number on a list ?
That is, A1 / B12 / C32 etc.