How to matching substring from a Cell

Dear Sir/Madam,

I am now having a column likes below

Column A
My Name is David Chan
Marry
David Williams
He is David and is a boy

I want to find out Column A cells with contact the word "David', then 
display "T" on Coloum B if found or "F" is not found. The result should be 
as following:

Column A                                 Column B
My Name is David Chan            T
Marry                                         F
David Williams                           T
He is David and is a boy            T

How can I do it ?
I have tried if( ), but it only match the cell with only input "David" in 
it.

Thanks for help.

Regards,
William


0
fatguy (13)
9/2/2008 2:03:25 AM
excel 39879 articles. 2 followers. Follow

4 Replies
1012 Views

Similar Articles

[PageSpeed] 27

Try this:

=IF(COUNTIF(A1,"*David*"),"T","F")

Copy down as needed.

-- 
Biff
Microsoft Excel MVP


"��H@work" <fatguy@fatguy.com> wrote in message 
news:OIRxVAKDJHA.1628@TK2MSFTNGP02.phx.gbl...
> Dear Sir/Madam,
>
> I am now having a column likes below
>
> Column A
> My Name is David Chan
> Marry
> David Williams
> He is David and is a boy
>
> I want to find out Column A cells with contact the word "David', then 
> display "T" on Coloum B if found or "F" is not found. The result should be 
> as following:
>
> Column A                                 Column B
> My Name is David Chan            T
> Marry                                         F
> David Williams                           T
> He is David and is a boy            T
>
> How can I do it ?
> I have tried if( ), but it only match the cell with only input "David" in 
> it.
>
> Thanks for help.
>
> Regards,
> William
>
> 


0
biffinpitt (3172)
9/2/2008 2:08:03 AM
Try this formula...

=IF(ISNUMBER(SEARCH("david",A1)),"T","F")

-- 
Rick (MVP - Excel)


"��H@work" <fatguy@fatguy.com> wrote in message 
news:OIRxVAKDJHA.1628@TK2MSFTNGP02.phx.gbl...
> Dear Sir/Madam,
>
> I am now having a column likes below
>
> Column A
> My Name is David Chan
> Marry
> David Williams
> He is David and is a boy
>
> I want to find out Column A cells with contact the word "David', then 
> display "T" on Coloum B if found or "F" is not found. The result should be 
> as following:
>
> Column A                                 Column B
> My Name is David Chan            T
> Marry                                         F
> David Williams                           T
> He is David and is a boy            T
>
> How can I do it ?
> I have tried if( ), but it only match the cell with only input "David" in 
> it.
>
> Thanks for help.
>
> Regards,
> William
>
> 

0
9/2/2008 2:11:43 AM
=IF(ISERROR(FIND("David",A1)>0),"F","T")

-- 
Steve

"��H@work" <fatguy@fatguy.com> wrote in message 
news:OIRxVAKDJHA.1628@TK2MSFTNGP02.phx.gbl...
> Dear Sir/Madam,
>
> I am now having a column likes below
>
> Column A
> My Name is David Chan
> Marry
> David Williams
> He is David and is a boy
>
> I want to find out Column A cells with contact the word "David', then 
> display "T" on Coloum B if found or "F" is not found. The result should be 
> as following:
>
> Column A                                 Column B
> My Name is David Chan            T
> Marry                                         F
> David Williams                           T
> He is David and is a boy            T
>
> How can I do it ?
> I have tried if( ), but it only match the cell with only input "David" in 
> it.
>
> Thanks for help.
>
> Regards,
> William
>
> 
0
AltaEgo
9/2/2008 2:15:55 AM
Thanks all for help.

"��H@work" <fatguy@fatguy.com> ���g��l��s�D:OIRxVAKDJHA.1628@TK2MSFTNGP02.phx.gbl...
> Dear Sir/Madam,
>
> I am now having a column likes below
>
> Column A
> My Name is David Chan
> Marry
> David Williams
> He is David and is a boy
>
> I want to find out Column A cells with contact the word "David', then 
> display "T" on Coloum B if found or "F" is not found. The result should be 
> as following:
>
> Column A                                 Column B
> My Name is David Chan            T
> Marry                                         F
> David Williams                           T
> He is David and is a boy            T
>
> How can I do it ?
> I have tried if( ), but it only match the cell with only input "David" in 
> it.
>
> Thanks for help.
>
> Regards,
> William
>
> 


0
fatguy (13)
9/2/2008 3:25:47 AM
Reply:

Similar Artilces:

Solver Limits for Constraints and Adjustable Cells
What is Solver's limits for constraints and adjustable cells? According to Microsoft Knowledge Base Article 75714 it is: 200 adjustable cells, and unlimited constraints for linear problems and 500 for non-linear problems for Excel 97, 98 and 2000. According to Frontline Systems, the developers of the Excel Solver, the limits are 200 adjustable cells, and 200 constraints for linear problems and 100 for non-linear problems. They do not refer to a specific version of Excel. There is a significant difference (in terms of the number of constraints) between the above two piece...

Copy and Paste in Excel, copies cell and formula, but shows same v
Excel 2003 with 2007 converters installed. Not constant problem, but have seen it once before, can't remember how to stop it happening. I have a bank forecast which I have a running total column, against a individual line item list. Noddy stuff. Problem. If I make changes to the forecast I need to recopy down the running total formula to refresh the running total when it doesn't pick up the additions, usual when a cut has been pasted in. Whilst it allows me to dragdown or copy and paste the formula and the new cells take on the correct line/column numbers to do thei...

Adding the contents of cells from other workbooks
Dear All I am trying to develop a tour sheet which has values in given cells. I have created a master tour sheet in another workbook so that when you input numbers on the original worksheet they transfer overto the master workbook. I want to be able to transfer data to the master workbook from multiple, separate worksheets/books (other people will have copies of the worksheet and fil it in.) This is where i am struggling is how can i get the data to add when other sheets are filed in as at the moment it just replaces the original data in the master copy instead of adding to it. Hope i have e...

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

How do I preserve text color when combining cell data
I'm using "&" to combine data from multiple cells. Each cell has text of a different color and I want to preserve those colors in the new combined cell. The new combined cell is formatting all the text to one color. Anyone have any suggestions on how to maintain the separate colors in the new cell? A formula can only return a value to a cell, so you cannot return formatting and such. =A1&A2 will return the value of A1 and A2 to the cell that has that formula entered in it. HTH Regards, Howard "CraigS" <CraigS@discussions.microsof...

Borders Don't Line up with Cells.
Hi, I have a spreadsheet where I have used borders of different types to highlight cells. However, recently the horizontal borders aren't working correctly. The lines don't match the text. The spacing of the line is bigger than the spacing of the text, and so the lines run through the text. Does anyone know what causes this and how I can fix it? I have tried two different printers and have the same problem, so I don't think it is a printer driver problem. Thanks. Michael ...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

not wordwrapping last two lines in cell
In cell 3C I have 18 lines that will wordwrap. the last two sentences will not wordwrap the last two sentences list about 10 words each and then run off the cell and cannot be seen. Any ideas how to fix this Thanks in Advance Does this link help: http://support.microsoft.com/default.aspx?scid=kb;en-us;211580 "badgercat" wrote: > In cell 3C I have 18 lines that will wordwrap. > the last two sentences will not wordwrap > the last two sentences list about 10 words each and then run off the cell > and cannot be seen. > > Any ideas how to fix this > > Tha...

copy & paste spreadsheet cells from excel to outlook to excel
I am trying to copy an example of 2 rows of 5 cells each into outlook to e-mail to 300 people so they can put info into 5 of the cells and reply back to me. When I receive this e-mail back I want to copy those 5 cells into a spreadsheet that already exists replacing what is there with this new information for each person. I have tried several methods of copy and paste but each time I paste back into excel I get two rows of cells, one row of empty cells and one with the new data. How can I avoid getting 2 rows of 5 cells when I am copying only one row of 5 cells? It is maddening! Send t...

Wrapping text in a cell
In a single cell, suppose I want text to appear on two lines. Viz: Case One Case Two How do I do that so that I specify the wrap point? Thanks! If you are typing the data into the cell use Alt-Enter between each string to indicate where you want a line break to occur. Case One<Alt-Enter>Case Two Alt + Enter -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27741 View this thread: http://www.excelforum.com/showthread.php?threadid=476428 If you...

Filling a Cell with Color
I know how to use conditional formatting to change the text color in a cell. But I'm at a loss as to how to make a cell "fill" with a color. It must be something simple. You should still be able to use Conditional Formatting... After clicking the [format...] button, there are 3 tabs: • Font • Border • Patterns<-------Use this one Does that help? *********** Regards, Ron XL2003, WinXP "RF" wrote: > I know how to use conditional formatting to change the text color in a > cell. But I'm at a loss as to how to make a cell "fill" with a color....

Excel 2002 : How to eliminate _ before a cell address in a formula
Hi, I am working on a report generated by a business system In Excel Format. The table in the Excel file is a Sub Total Table that is expandable. I need to manipulate on the data of the expanded table, however noted that the formula below could not work as I copy downwards. The second and third argument of the formula is fixed by "_" before the cell address E17 and D17. =IF(LEFT(P10,1)="5",_E17,-_D27) Also I find that when I move the cursor to the cell for the 2nd and 3rd argument, the resulting cell address that appears in the formula is not the...

Extracting substrings in CString
I have a CString which contains a number of substrings separated by a ",". How would I extract each substrings into a CArray of CString? look up _tcstok in MSDN "Charles Tam" <CharlesTam@discussions.microsoft.com> wrote in message news:8CD0AF17-3F44-4D88-98F4-C14DCAE5BA44@microsoft.com... >I have a CString which contains a number of substrings separated by a ",". > > How would I extract each substrings into a CArray of CString? > > Look at the tokenizer class here: http://www.codeproject.com/string/tokenizer.asp --- Ajay ...

city, state, zip in same cell
I ha ve been given a large data base with city, state and zip in same cell. How can I seperate the city, state and zip without doing it manually? Good afternoon, u may want to try this. First insert a few columns right after the column that contains the city state, etc. info. Then click on the column letter to highlight the column that contains the city, state, etc. data u would like to split into cells, then goto DATA menu toward the top of your screen and click on TEXT TO COLUMNS. A box will pop up, make sure DELIMITED button is on and click NEXT, Then put a check in boxes by COMMA a...

Automatically updating cells in one Excel file to another
Hi All, I'm currently updating a project around staff attendance that I'm doing in Excel which will then go out to all of my offices. The thing is that as we are now a couple of weeks or so into the financial year I wanted to save my office managers the task of inputting all their staff's start and finish times over again. If the original file is called Staff1.xls and the new one Staff2.xls, is there a formula I could put in say E4 of the new one (Staff2.xls) which would check to see if the other file (Staff1.xls) was open and if so copy over the details. I know I can do ...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

How do I count one cell based on another cell?
How do you count the number of cells with data (not blank) in a given range based on a criteria in a different range? for example, how many corresponding cells are populated with data in c1:c1000, if b1:b1000 = SMU? I have tried so many variations of the the following to no avail. =COUNT(IF(b1:b1000,"SMU",counta(c1:c1000))) If anyone can resuce me...I am frustrated...what am I missing? Harr Hi try =SUMPRODUCT(--(B1:B1000="SMU"),--(C1:C1000<>"")) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany &qu...

Sum of every nth cell in a column
I am trying to figure out the formula for adding the nth cell in a given column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) That is an array formula so it must be committed with Shift + Ctrl + <Enter>. here is how it works... Moving down through cells C2:C213 it looks at each 2 things. The value of the cell and the row that cell is on. If the row that cell is on is +1 is evenly divisible by 1 then ...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

How to generate a cell reference from multiple cells
For example, to acquire the cell reference 'c5' the C is generated by a formula in one cell, and the 5 is generated in a different cell Try this... A1 = C B1 = 5 =INDIRECT(A1&B1) Which evaluates to =$C$5 -- Biff Microsoft Excel MVP "Malters" <Malters@discussions.microsoft.com> wrote in message news:F8BDB88A-7371-4C02-8973-B7E8F3E5E50D@microsoft.com... > For example, to acquire the cell reference 'c5' the C is generated by a > formula in one cell, and the 5 is generated in a different cell ...

Force a new line in a cell with "wrap text" on
How can I put a newline command in a cell formula? I've tried using Alt-Enter, but it doesn't work. Why would you want to wrap a formula? Do you mean text, or an actual formula? "DRARetired" <draretired@sbcglobal.net> wrote in message news:edf201c43d16$e7f8e5c0$a001280a@phx.gbl... > How can I put a newline command in a cell formula? I've > tried using Alt-Enter, but it doesn't work. In a Worksheet use CHAR(10) in a macro use CHR(10) Alt+Enter automatically turns on Cell Wrap. This won't so you may have to format the cells yourself. Fo...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

Average only cells >0
Is there any way to create a formula that takes an average of all cells in range that are greater than the value of zero? If the value is zero, it should not be included in the average. Thank you! ...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...