search function

I am trying to search fro two pieces of infomation from one cell.
For one piece i have used =IF(LEFT(J11,2)="Ph",L11,"").  What i want to be 
able to do is search for either "Ph" or "In" from the same cell.  Is this 
possible?  I think i have seen this done by setting a special function and 
VB!!
0
red (30)
3/2/2009 11:27:01 AM
excel 39879 articles. 2 followers. Follow

6 Replies
383 Views

Similar Articles

[PageSpeed] 48

Focusing on just this line
> .. i want to .. search for either "Ph" or "In" from the same cell

One way, you could use this test in say, B1, copied down:
=SUMPRODUCT(--ISNUMBER(SEARCH({"Ph","In"},A1)))>0
Adapt to suit

If you need it to be a stricter case sensitive search, 
replace SEARCH with FIND

High-five? Click YES below   
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
--- 
"red" wrote:
> I am trying to search fro two pieces of infomation from one cell.
> For one piece i have used =IF(LEFT(J11,2)="Ph",L11,"").  What i want to be 
> able to do is search for either "Ph" or "In" from the same cell.  Is this 
> possible?  I think i have seen this done by setting a special function and 
> VB!!
0
demechanik (4694)
3/2/2009 11:57:01 AM
or just

=SUMPRODUCT(--(LEFT(J11,2)={"Ph","In"}))>0

-- 
__________________________________
HTH

Bob

"Max" <demechanik@yahoo.com> wrote in message 
news:69A78E62-94E2-4998-A271-42BBFD6F39E5@microsoft.com...
> Focusing on just this line
>> .. i want to .. search for either "Ph" or "In" from the same cell
>
> One way, you could use this test in say, B1, copied down:
> =SUMPRODUCT(--ISNUMBER(SEARCH({"Ph","In"},A1)))>0
> Adapt to suit
>
> If you need it to be a stricter case sensitive search,
> replace SEARCH with FIND
>
> High-five? Click YES below
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:23,500 Files:370 Subscribers:66
> xdemechanik
> --- 
> "red" wrote:
>> I am trying to search fro two pieces of infomation from one cell.
>> For one piece i have used =IF(LEFT(J11,2)="Ph",L11,"").  What i want to 
>> be
>> able to do is search for either "Ph" or "In" from the same cell.  Is this
>> possible?  I think i have seen this done by setting a special function 
>> and
>> VB!! 


0
BobNGs (423)
3/2/2009 3:32:57 PM
Thanks guys, works a treat!!

"Bob Phillips" wrote:

> or just
> 
> =SUMPRODUCT(--(LEFT(J11,2)={"Ph","In"}))>0
> 
> -- 
> __________________________________
> HTH
> 
> Bob
> 
> "Max" <demechanik@yahoo.com> wrote in message 
> news:69A78E62-94E2-4998-A271-42BBFD6F39E5@microsoft.com...
> > Focusing on just this line
> >> .. i want to .. search for either "Ph" or "In" from the same cell
> >
> > One way, you could use this test in say, B1, copied down:
> > =SUMPRODUCT(--ISNUMBER(SEARCH({"Ph","In"},A1)))>0
> > Adapt to suit
> >
> > If you need it to be a stricter case sensitive search,
> > replace SEARCH with FIND
> >
> > High-five? Click YES below
> > -- 
> > Max
> > Singapore
> > http://savefile.com/projects/236895
> > Downloads:23,500 Files:370 Subscribers:66
> > xdemechanik
> > --- 
> > "red" wrote:
> >> I am trying to search fro two pieces of infomation from one cell.
> >> For one piece i have used =IF(LEFT(J11,2)="Ph",L11,"").  What i want to 
> >> be
> >> able to do is search for either "Ph" or "In" from the same cell.  Is this
> >> possible?  I think i have seen this done by setting a special function 
> >> and
> >> VB!! 
> 
> 
> 
0
red (30)
3/2/2009 6:20:06 PM
"red" wrote:
> Thanks guys, works a treat!!

But .. somehow you din't high-five my response? The expression addresses the 
generic scenario where you're fuzzy searching for the existence of a couple 
of substrings within a cell. This could come in real handy one fine day.  
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
0
demechanik (4694)
3/3/2009 12:01:01 AM
Maybe, judging from the original post, that solution is too generic and 
wrong if it counts say Ph within a cell whereas he/she only wants to count 
when it starts.

-- 
__________________________________
HTH

Bob

"Max" <demechanik@yahoo.com> wrote in message 
news:166F9AF7-825A-4C56-BD84-BC204B5331EF@microsoft.com...
> "red" wrote:
>> Thanks guys, works a treat!!
>
> But .. somehow you din't high-five my response? The expression addresses 
> the
> generic scenario where you're fuzzy searching for the existence of a 
> couple
> of substrings within a cell. This could come in real handy one fine day.
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:23,500 Files:370 Subscribers:66
> xdemechanik
> --- 


0
BobNGs (423)
3/4/2009 8:04:11 AM
I qualified the focus line in my response, Bob
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
--- 
"Bob Phillips" <BobNGs@somewhere.com> wrote in message 
news:uaktY$JnJHA.5124@TK2MSFTNGP03.phx.gbl...
> Maybe, judging from the original post, that solution is too generic and 
> wrong if it counts say Ph within a cell whereas he/she only wants to count 
> when it starts.


0
demechanik (4694)
3/5/2009 11:36:44 PM
Reply:

Similar Artilces:

how to disable the input function in the CCombobox
Change it's type to drop list. AliR. "joddy" <zsy2000@citiz.net> wrote in message news:%23pvpTXhWHHA.4132@TK2MSFTNGP06.phx.gbl... > > Under what conditions? If you just want something that doesn't change, use the Drop List capability. If you need to disable a control temporarily, you can subclass the edit control portion of the combo box and capture and ignore user input. What do you want to do? joe On Tue, 27 Feb 2007 10:20:16 +0800, "joddy" <zsy2000@citiz.net> wrote: > Joseph M. Newcomer [MVP] email: newcomer@flounder.com Web:...

VLookup Function over Multiple Worksheets
I am trying to use the VLOOKUP function over multiple worksheets, can this be done on Excel 2003? I am trying to automatically retrieve data that is dependent on previously entered fields in drop down lists that I have created. Regards, A semi-exoctic formula from Peo S a few years ago, this looks up across eight sheets. It is an array-entered formula... CTRL+SHIFT+ENTER If you wnt to tackle this I will help you, I don't completely understand the formula but I believe I can guide you through it to lookup over many worksheets. (formula is all one one line in both cases...

Tender buttons on touchscreen not functioning in Store Operations
Tender buttons on touchscreen not functioning in Store Operations I found Article ID : 862004 on customer source. But I am confused after step 9 After I run query for step 4 I found this ID Description ADDITIONAL DETAIL Display Code 1 CASH 1 0 1 2 CHECK 2 1 2 3 MASTER/VISA 3 4 3 4 CASH OVER 1000 1 9 ...

How do I undo the alt+enter function?
Hi not sure what you're trying to do. Do you want to delete a manual linebreak? -- Regards Frank Kabel Frankfurt, Germany "JennyB" <JennyB@discussions.microsoft.com> schrieb im Newsbeitrag news:2D990E6E-E599-4566-9D51-2158C56F9898@microsoft.com... > highlight the cell you want to change, on the main menu selec Format>cells (CTRL+1), click the 'Alignment' tab and uncheck Wrap Text Your text line now shows small squares at the point of each carriag return. Press F2 (edit) and delete the squares (they may not b visible in edit mode, but they are still the...

Workbook pivot table design function
I used Sheet1 as the data source to create a pivot table in a new worksheet which I named Sheet PT. Is it possible to design the workbook so that I can delete the data in Sheet1 and then paste new data with more or less rows in Sheet1 and have the pivot table in Sheet PT update to reflect the new data that has been pasted into Sheet1 but the formating of the pivot table stays the same? One thought is to simply set the pivot's source range (In step 2 of the PT wizard) to cover the max expected extent of source data at the onset, then configure the base PT format. Then jus...

email not functioning
I have joined outlook expess since more than a year ago. but i am not receiving mail nor have been able to send any email.Please sysnronise. 1. Check-in at http://windowsupdate.microsoft.com & get that computer fully-patched. 2. See... How to access your Hotmail account in Microsoft Outlook Express http://windowslivehelp.com/solution.aspx?solutionid=99d4b13d-13db-40d8-9cdf-172002d4194c -- ~Robear Dyer (PA Bear) MS MVP-IE, Mail, Security, Windows Client - since 2002 kRUShNa wrote: > I have joined outlook expess since more than a year ago. but i am not > rece...

Which function to use to open an .asc file?
Hi Im writing a vba program to take the info from an asc file into sheet2. I must use the xldelimited so the info goes into each column properly and not just everything in one column ( it uses the comma to determine the column). The intention of the program is, in sheet1, a command button is used to open a file window. The asc file is then selected bu the user, and the data is to placed into sheet2. The asc file is no longer needed. Calculations are made and shown in sheet1 using the data in sheet2. The problem is Im not sure what function to use. Im thinking this one: object.OpenAsTe...

Best ways to search an array/collection for an element
Hi guys! I'm creating a small library of utilities for VBA programming, and right now I'm completing my ArrayManagement and CollectionManagement module. I was considering different solutions for the classical case of searchin an array or collection for a specified elements. So far I've considered using Worksheetfunction.VLOOKUP, Worksheetfunction.Match (needs some care to take into account the "element not found" case) for the arrays. I also developed an handy binary search function for sorted arrays, but it works only for 1D numeric arrays (sorted in either no...

OL2007
Running OL2007 SP2 under Vista Home Premium SP2. It appears that when I'm replying to a plain text message, editing functions (bold, underline, etc) are disabled. Is there a way to enable them? You'd need to switch to HTML format - plain text is, well, plain text. That means no formatting. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchan...

Error: "Search key was not found in any record"
Dear all, Recently, my Access 2000 is upgraded to XP version. After convert all 2000 mdb file to 2002, I've got a big trouble. 1.Path of all link tables cannot be changed. 2.Saving a new form dialog box repeat and repeat again when save a new form. 3.New saved form cannot be open. 4.Form cannot be deleted. A same error message is found in above case: "Search key was not found in any record" Could any one help me? Thank you very much! Billy "Billy" <billysoo@hotmail.com> wrote: >Recently, my Access 2000 is upgraded to XP version. >After convert all 20...

Functions showing as text
Just recently when I type a function into certain cells, the function shows up as text. If I type the function into a cell elsewhere on my sheet and then cut and paste it into the original cell all is ok. I have tried changing the formatting of the cell to General, Number, etc and it makes no difference. I have also tried clearing the contents and formatting; again to no avail. Any ideas? Sandy Maybe... Saved from a previous post. Excel likes to help. Try this on a test worksheet. Select A1 and hit ctrl-; (to put the date in the cell) now select B1 and type: =a1 Notice that exc...

search form not to load all data up to start with
I have a form based on a query that i use to search for product in my database, on the form i have text box to type in, then a button to requery the query has the following Like "*" & [forms]![frmProductList]! [txtProductSearch] & "*" This query works fine but when i load up the form is shows all the products we stock which is a lot and seems to take a lot longer to load now, Is there a way for the form not to load up any data until o press the search button Try putting a default in [forms]![frmProductList]![txtProductSearch] that would not pro...

Providing Undo
Hi... I am working on an application(in this we can create objects of about 60 different classes and delete and can modify the behavior of object and finally we can save the file which can be loaded later) presently supporting binary serialization. But in a new requirement I have to provide XML serialization which I have done by using my own methods. But the problem here is that I have to support undo -redo functionality of objects. The existing Undo -redo functionality in this application main logic of which is as follows: A undo stack is created and objects are pushed into the stack w...

Unable to search Accounts, message says 'No Attribute'?
I'm having a problem with the search utility in CRM 3.0. When I try to perform a search on an account in the 'Active Accounts' View (whcih is set to the Default Public View) all I get when I click the 'Find' button is a message box that says 'No Attribute' with an OK button on it. The error started on a new Server and CRM install when I was trying to create an association between a custom entity and another entity. SQL Server took all the 2GB of ram and locked up the server. After about 5 minutes I paused SQL then Stopped it and then rebooted the server. Whe...

IE8 address box steals focus from search box
Hi Running latest IE8 I keep finding that when I start IE8, type an expression in the search box in the top right corner, the address box steals focus So if I type "SQL Server 2008 Client installation", the letters "SQL S" are in the search box and the rest of the letters "erver 2008 Client installation" are now in the address box. This is deeply annoying. Anyone seen this and know how it can be fixed? It only seems to do this at IE8 startup or new tab startup. After that, all words are in the search box and the address box dioes not steal focus....

Max function
I have a column of numbers. some of which are postive, some are negative. I want to find the maximum number i.e If there are 2 numbers, 100 and -200, i want it to return the value of 200. I have tried the MAX function but this returns a value of 100 in this example. thanks Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of just Enter): =MAX(ABS(A1:A10)) Note: You can avoid the C+S+E with this version: =MAX(INDEX(ABS(A1:A10),0)) Change range references to suit your situation. Does that help? -------------------------- Regards, Ron (XL2003, Win XP) Microsoft MVP (...

Rosters
Hi, Not sure if this can be done. A continuous roster of 3 panels (teams) working 7 days, 7 nights, then 7 off, the three panels overlap to ensure coverage.. is there a function that will assist with this? Thanks Not really but there are those that have made these http://blog.jausovec.net/index.php?op=ViewArticle&articleId=165&blogId=1 -- Regards, Peo Sjoblom Portland, Oregon "KAT" <KAT@discussions.microsoft.com> wrote in message news:08177986-20F5-41BD-95B2-37641EB89F84@microsoft.com... > Hi, > > Not sure if this can be done. A continuous roster...

Need a Function for Grouping
I have a question. I am new to Excel and I have been challenged with a task of grouping students that need the same course in Excel 2007 by my employer. Example: Billy needs Math, Science, and English; Jill needs Math and English; Eric needs only Science; Jane needs Math, Science, and English; Adam needs Math and English. How would I group 50+ students that need the same course, and what type of formula do I use? I need them to group like: Group A, Group B, Group C, etc... Ingus Smith;219998 Wrote: > I have a question. I am new to Excel and I have been challenged with a > task &...

What function would I use? #2
Thanks for the suggestion it did work really well, but I think I'm dealing with a little too much data for it. This is an example of what I'm trying to do. Product 1 -------------------------------------------------------- Customer name | accesorie a |accesorie b| accessorie c | -------------------------------------------------------- Bob | yes | | | -------------------------------------------------------- Mary | | yes | | ------------------------------------------...

tried using SUBTOTAL function (XL XP) and it doesn't seem to work
tried using SUBTOTAL function (XL XP) and it doesn't seem to work =SUBTOTAL(9,A1:A10) still gives total of rows 1 thru 10 even though i've hidden rows 5 thru 7 ??? is there any other method of working with visible cells only? Jethro, SUBTOTAL works on filtered data not hidden. You would need a UDF. Here is a previous one from Bernoe Dietrick You can use a UDF, definition below, used like =mySum(A1:A10) Copy the code and paste it into a module in your workbook. Function mySum(inRange As Range) Dim myCell As Range For Each myCell In inRange If Not myCell.EntireRow.Hidd...

Sorting Search Results
I there a way to sort search results to see the messages with the latest responses first? Thanks, ...

searching for specific text
well, maybe you'll know how to do it with the bigger picture. I'll give you an ‎example: there is a textbox column that might contain the sentence "a box of ‎chocolate". I have another column that needs to say what kind of candy (from a ‎known list of possibly candy) it is the first column. now I need a function that will ‎automatically finds which of the keywords is in the textbox column and gives the ‎answer in the second column.‎ Hello, If you do not get a reasonable response, please send the workbook to: excelmarksway@yahoo.com.au Any confidential information recei...

Converting functions into numbers
For example, using the function =2*3, I will get a 6 in the cell. Now I would like to copy the 6 into another cell. Using the usual copy, I will copy the function. Is there a way that I can copy the 6 as a number and not as a function -- vesuviu ----------------------------------------------------------------------- vesuvius's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2857 View this thread: http://www.excelforum.com/showthread.php?threadid=48237 Sorry to bump the board, but I really need help fast -- vesuvius ----------------------------------------...

Right Click Function
The right click function does not work in Excel. However, it works just fine in all other applications. What am I missing? Hi Mollie, What does the right-click not work on, you probably had a macro or addin fail. Think of what you were last working with before you had your problem. If you had been working with macros, I'd give the following a shot. Sub Fix_Things() Application.Commandbars("Ply").Enabled = True 'Rclick on WS Tab Application.DisplayAlerts = True Application.EnableEvents = True 'WS Events End Sub --- HTH, David McRitchie, Micro...

HELP!!!!! (apo on worksheet functions)
I am working on a spreadsheet and when you "look" at the cell it looks like this: 'S000000200BX00000001000 . I need to figure out how to do an auto sum on these. I was messing around and did a few find and replace and got rid of the 'S thing and now I have a formula that looks like this... 2E+13... what is going on with this crazy thing?? This is a spreadsheet that is not made with in my company and I am not sure of how it was created! ANY HELP IS GREATLY APPRECIATED!!! PLEASE!!!!!!!! Excel fails at over 15 digits. What part (s) are you trying to sum??? -- Don ...