Searching for text within cells

I want to create an IF statement that does something like this:

(Formula in cell B1) - IF A1 contains "red" then return "red", else if A1 
contains "blue", return "blue", else if A1 contains "black" return "black" 
else .... and so on. I realise I have a 7 nested if limit. 

Can anyone help?

TIA, Alan
0
me3529 (37)
6/22/2004 1:32:11 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
707 Views

Similar Articles

[PageSpeed] 1

Well. The way you stated it
=a1
would do it.

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Alan" <me@here.now> wrote in message
news:Xns951061569AB0Dmeherenow@207.35.177.135...
> I want to create an IF statement that does something like this:
>
> (Formula in cell B1) - IF A1 contains "red" then return "red", else if A1
> contains "blue", return "blue", else if A1 contains "black" return "black"
> else .... and so on. I realise I have a 7 nested if limit.
>
> Can anyone help?
>
> TIA, Alan


0
Don
6/22/2004 1:56:47 PM
Hello Alan
There may be simpler but anyway here's a try:
=INDEX({"blue";"red";"black"},MATCH(A1,{"blue";"red";"black"},0))
And you may add some test with somthing like :
=IF(NOT(ISERROR(....

HTH
Cordially
Pascal

"Alan" <me@here.now> a �crit dans le message de
news:Xns951061569AB0Dmeherenow@207.35.177.135...
> I want to create an IF statement that does something like this:
>
> (Formula in cell B1) - IF A1 contains "red" then return "red", else if A1
> contains "blue", return "blue", else if A1 contains "black" return "black"
> else .... and so on. I realise I have a 7 nested if limit.
>
> Can anyone help?
>
> TIA, Alan


0
6/22/2004 2:02:27 PM
Alan,

  =INDEX({"Blue";"Red";"Black"},SUMPRODUCT((
   COUNTIF(A1,"*"&{"Blue";"Red";"Black"}&"*")>0)
   *ROW($1:$3)))


--
Regards,
Soo Cheon Jheong
Seoul, Korea
_  _
^��^
 -- 


0
exceler (34)
6/22/2004 5:11:04 PM
Reply:

Similar Artilces:

Hyperlink via indirect cell reference
Hi I have workbook that contains a number of sheets. On a separate sheet I would like to be able to insert a hyperlink so that I can jump to a specific sheet. However, rather than inserting all of the hyperlinks manually (I will have to replicate this over many workbooks) I wondered if there was a formula to allow me to jump to a cell (say A1) in another worksheet, based on the name of that worksheet being entered in a cell reference. For example - a number of worksheets called "Sheet1", "Sheet2", "Sheet3", "Sheet4". In another sh...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

Autofilter to search characters that begin with "* "
I'm having difficulty getting Autofilter to work with text that begins with "* ". It appears that * is a wild card, but I don't want to search that way. Any suggestions? Thanks, Barb Reinhardt So..you're trying to use a custom filter within Autofilter? Use Begins with and then, in the text box, precede the asterisk with a tilde ~* "Barb Reinhardt" wrote: > I'm having difficulty getting Autofilter to work with text that begins with > "* ". It appears that * is a wild card, but I don't want to search that > way. Any s...

How do I freeze or lock cells to show up on each page without typ.
I have a 4 page sheet. I have a header already. But I want to freeze the cells that head up the first page. I've done it before in school but can't remember what it is called or how to do it...that's why I'm doing this. Anyway, I want these cells to print off on each new page without having to type them on each page. I hope that makes sense and I hope that someone can help me! If you mean for printing do file>page setup>sheet and select rows to repeat at top otherwise for viewing you can select a2 if the headers start in row 1 and do window> freeze panes ...

Rename Cell
How can I rename column A to read "bills" instead of the letter A? You can't. The closest you will get is to hide column headings, via Excel Option, and then create your own. -- Regards Dave Hawley www.ozgrid.com "shoe" <shoe@discussions.microsoft.com> wrote in message news:DBA970DF-D928-41EE-9565-4639E7D49BCE@microsoft.com... > How can I rename column A to read "bills" instead of the letter A? you cant change the headers or row labels but you can define you data as a list (or table) and the headings can then be used to refer...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Cell Format #4
Is there a way to have a cell format based on contents of an i statement... Example if(C1="Input",and(C3,Format $#.##),if(C1="% of Revenue",and(C5,Forma #.##%),na) I want the If statement to test a condition, return contents of th correct cell and format automatically. Any help is appreciated -- bforster ----------------------------------------------------------------------- bforster1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1177 View this thread: http://www.excelforum.com/showthread.php?threadid=26133 You can't change the fo...

Getting contents of adjacent cells
I want to divide the y1-axis column and save it to radius (y1/2) column. How do I do that? x-axis y1-axis radius(y1/2) 0 0.00 8.0000 1 0.25 8.0242 2 0.50 8.0691 3 0.75 8.1281 4 1.00 8.1989 5 1.25 8.2803 6 1.50 8.3716 7 1.75 8.4729 8 2.00 8.5832 hi divide the y1-axis by what? 2 as an guess with y1-axis in column c in the y1/2 column(d?), enter =C2/2 copy down. regards FSt1 "Rocky" wrote: > I want to divide the y1-axis column and save it to radius (y1/2) column. How > do I do that? > > x-axis y1-axis radius(y1/2) > 0 ...

Text Very Small When Replying
I'm able to change the text size when I'm replying to an email but how does one set the default size? No matter what I ser the font size to when replying it always reverts back to the smallest size when I do another reply. Look, I'm 54! I can't read that small text! James Are you using stationery? Also, Alt + T, Options, Compose,....... -- Ron Sommer MS MVP-Mail "JamesJ" <jjy@darwin_roadrunne0r.com> wrote in message news:OGt2E1OhKHA.1460@TK2MSFTNGP06.phx.gbl... > I'm able to change the text size when I'm replying to an emai...

Extract text from field
If you have a filed that contatins the following data:LastName, FirstNameWhere LastName has varing lengths. Can you run an update query to obtain just the LastName part of the field? If so, what would be the command?Many, many thanks in advance. On Tue, 6 Mar 2007 14:09:45 -0500, "Mary M" <none@no_email.com> wrote:>If you have a filed that contatins the following data:>>LastName, FirstName>>Where LastName has varing lengths. Can you run an update query to obtain >just the LastName part of the field? If so, what would be the command?>>Many, many thanks in...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Adding a certain text label in a excel chart
I am plotting in regularly basis a certain set of data in excel. Based on some data analysis this set of data has to be fitted to these equations: y = 1/x^a (1) and /or y = b/x^c (2) from data analysis, constants a, b and c are found and are placed lets say in cells A1, B1, C1. On my graph, I am putting then two small text labels where the real equation is displayed: smth. like: y = 1/ x^3.45 and / or y = 0.256 / x^3.12 The whole process is similar with excel curve fitting, when the “show equation on chart” is checked. Thank you in advance My question is: Can ...

cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop selecting cells. Have tried double clicking, playing with the Function keys, all sorts of things, but to no avail... don't want to force quit. Any clues? TIA, Geri Hi Geri, See David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/ghosting.txt --- Regards, Norman "Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in message news:438C3854-C74C-410A-BD88-DAA146172E99@microsoft.com... > All of a sudden the mouse is acting like it is held down, a...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

Average of logic cells
I used a logic test to determine some levels from raw scores. For EG >120 =5, 119-110 = 4, etc. I now want to dtermine an average score of several of the the results from the logic tests but it doesnt seem to work. (AVG does not recognise cells with logic tests) Can anyone help, please? -- ckdkvk ------------------------------------------------------------------------ ckdkvk's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29245 View this thread: http://www.excelforum.com/showthread.php?threadid=489704 hi, ckdkvk ! > I used a logic test to determine so...

Formatting Linked Cells
I have a project to do. I have to create an input worksheet that is the originator of other worksheets that are linked to the input worksheet. Is there a way to have the linked cells shown as a blank cell if the data (especially text data) is not enter in the input worksheet yet. MT Hi =IF(YourLink="","",YourLink) -- Arvi Laanemets (Don't use my reply address - it's spam-trap) "MT" <MT@discussions.microsoft.com> wrote in message news:5398D6F8-1554-46BB-B009-CCE3183C80ED@microsoft.com... > I have a project to do. I have to create an input ...

Cell References..
I have a 12 month rolling report with a seperate worksheet within th workbook which refers to the column containing current month's Numbers When I "Cut" Column C (which contains the oldest Month) and "insert column C between N & O it shifts my cells left and all I need to do i input all of the current Month Data into Column N. The formulas al remain intact and everything is peachy. Until I goto the Workshee that refers to the Current Month on the 12 month rolling report. My problem is that when I shift the columns on the "Report" workshee it chages the cell...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Changing named range reference depending on a cell's content
Where to start?! I've got the following formula pulling data in from a secon spreadsheet within the same workbook: =IF($I$7="MICH",INDEX(MICH,MATCH($D7,LOB,0),MATCH($F$5,Month,0)),0) We have 8 different locations ("MICH" being one of them) that we nee to be able to access. I can write a nested IF formula that looks a cell I7 (that contains a list of all 8 locations) and, depending o I7's content, brings back the desired values. I was hoping someone in the forum could help me write a simpler formul that would not have 7 IF statements embedded in it. Any help w...

greyed out auto text
Created custom toolbar in Word with menu, autotext and email button as directed in "Quick sales letters for e-mail" demo video, but when I click email button, new email message appears in Outlook but auto text choices are greyed out???? Any ideas?? ...

insert text to different pages and update
Is there a way to insert a text cell from one page put into another page and when the first page cell is updated the second page cell will also be updated. Using the = sign just displays the formula, not the text. :confused: Thanks Frank -- fwburkey ------------------------------------------------------------------------ fwburkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30314 View this thread: http://www.excelforum.com/showthread.php?threadid=505331 Not clear what you are doing wrong. the formula should look lik =Sheet1!A1 Make sure there is noth...

Color change in cell when > 49.99
I need a cell to change color if the value inside the cell reaches 50 or higher.either text or cell shade. just so it catches the users attention. im running excel 2000. and i have this currently in the cell that i want to aplly this to: =HLOOKUP(D20,'Hidden Data'!GZ10:HB11,2,0)*MAX(15,E20) have you tried conditional formating? format>conditional formating >-----Original Message----- >I need a cell to change color if the value inside the cell reaches 50 or >higher.either text or cell shade. just so it catches the users attention. >im running excel 2000. and i ...

Cursor on new message window but no text appears when typed?
When using Outlook 2003 for sending new e-mail, I'm unable to type text into the message body. The cursor appears but no text shows. Text appears when I use Word 2003 to send my new e-mail message. It is more convenient for me to use Outlook. Help. ...

Text Box & Border Art
New Publisher 2003 user and getting quickly frustrated. I have setup a custom page size of 6.5" x 4.5" under Page Setup/Print Settings. I then created a text box of the same size, and filled it with the text I wanted. I then want to add a border to this text box. So, I right click on the text box, and choose Border Art under the Colors and Lines tab, and select the border "Twisted Lines" and hit apply. All is good. Now comes the trouble. I hit print preview and about 1/8" around the entire page is cutoff. All the margins are set to 0". I'm at a ...