(v)lookup part of text in cell

Hi.

Can anybody solve this problem in excel? 
I want to lookup a part of a word in cells fa searching for john and get a
"responce" like this:

Johndoe  |  yes 
john,doe |  yes
doejoHn  |  yes
samual   |   
Johnace  |  yes
pete     |

I was thinking it could be possible with using wildcards in the vlookup
function, but I don' t know how. It would be nice if capitals are ignored.

Tia
Ted




0
ted221 (2)
7/22/2005 7:43:40 PM
excel 39879 articles. 2 followers. Follow

4 Replies
463 Views

Similar Articles

[PageSpeed] 20

Hi,

yes, you can use wildcards with vlookup:

=IF(ISERROR(VLOOKUP("*john*",A1,1,0),"","yes")

or you can do this:

=CHOOSE(ISNUMBER(SEARCH("john",A1)+1,"yes","")

or:

=IF(ISNUMBER(SEARCH("john",A1),"yes","")

or:

=--ISNUMBER(SEARCH("john",A1)
and then format cell using the following custom style: [=0]"";"yes"

Regards,
KL


"Ted" <ted@ocalhost.localdomain> wrote in message 
news:pan.2005.07.22.19.43.40.277116@ocalhost.localdomain...
> Hi.
>
> Can anybody solve this problem in excel?
> I want to lookup a part of a word in cells fa searching for john and get a
> "responce" like this:
>
> Johndoe  |  yes
> john,doe |  yes
> doejoHn  |  yes
> samual   |
> Johnace  |  yes
> pete     |
>
> I was thinking it could be possible with using wildcards in the vlookup
> function, but I don' t know how. It would be nice if capitals are ignored.
>
> Tia
> Ted
>
>
>
> 


0
7/22/2005 8:45:22 PM
Try this:

=IF(ISNA(MATCH("*john*",A1,0)),"","Yes")

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Ted" <ted@ocalhost.localdomain> wrote in message
news:pan.2005.07.22.19.43.40.277116@ocalhost.localdomain...
> Hi.
>
> Can anybody solve this problem in excel?
> I want to lookup a part of a word in cells fa searching for john and get a
> "responce" like this:
>
> Johndoe  |  yes
> john,doe |  yes
> doejoHn  |  yes
> samual   |
> Johnace  |  yes
> pete     |
>
> I was thinking it could be possible with using wildcards in the vlookup
> function, but I don' t know how. It would be nice if capitals are ignored.
>
> Tia
> Ted
>
>
>
>


0
ragdyer1 (4060)
7/22/2005 8:51:47 PM
One more:
=if(countif(a1,"*john*")>0,"Yes","")



Ted wrote:
> 
> Hi.
> 
> Can anybody solve this problem in excel?
> I want to lookup a part of a word in cells fa searching for john and get a
> "responce" like this:
> 
> Johndoe  |  yes
> john,doe |  yes
> doejoHn  |  yes
> samual   |
> Johnace  |  yes
> pete     |
> 
> I was thinking it could be possible with using wildcards in the vlookup
> function, but I don' t know how. It would be nice if capitals are ignored.
> 
> Tia
> Ted

-- 

Dave Peterson
0
petersod (12004)
7/23/2005 12:00:00 AM
Thanks guys, lots of sollutions :) 






0
ted221 (2)
7/24/2005 9:35:12 AM
Reply:

Similar Artilces:

Customized Text Fields
I am having a problem seeing the data from customized text fields in the Resource Usage view. For example I have a text field for charge numbers that are assigned to each task. I can view it in the Ghantt Chart. However, when I go to the Resource Usage view and insert that text column the data doesn't appear. -- DJW In article <2A53FDE3-F008-4134-838C-268B567209F8@microsoft.com>, DWilliams <DWilliams@discussions.microsoft.com> wrote: > I am having a problem seeing the data from customized text fields in the > Resource Usage view. For example I...

Scatter Chart Axis as text in C# Web appl
Hi, I've created a Scatter Chart in OWC11 in C# and the X axis must display dates instead of numbers, I do it using the code below: .... string x= "10/2/2004 12:00:00 AM\t10/2/2004 12:00:00 AM\t10/5/2004 12:00:00 AM\t10/5/2004 12:00:00 AM\t10/6/2004 12:00:00 AM\t10/7/2004 12:00:00 AM\t10/8/2004 12:00:00 AM\t"; string y = 1\t2\t76\t67\t...."; objChart.SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimCategories, (int)OWC11.ChartSpecialDataSourcesEnum.chDataLiteral, "Scatter Charts Series 1"); objChart.SeriesCollection[0].SetData (OWC11.ChartDimensi...

Copy contents of cells if cell contains information
I have a column of cells, say A1:A30 in a few different spreadsheets. Some of these spreadsheets contain information within these cells (not formulae, but just typed info) and some don't contain information. Now, in a new spreadsheet, I would like to copy the contents of these cells (columns) if there is information in them. So for example, in spreadsheet 1, i have information, speadsheet 2 i don't, spreadsheet 3 I do, i would like my information to be copied into a new cell as follows:- information 1 information 2 Any advice? Thanks Hi, Test this on sample (or backed-up) work...

How do I change the text on each label? I dont want the same info.
I want to print labels 6 per page; however, I want to control whats on each label. Only one label appears on screen at one time. How do I get all the labels to appear on the screen prior to printing so I can modify each one. You will have to setup your page manually. Take a look at the margins and gaps when you select your preferred label. Using these dimensions -- for example, label 5164 -- Arrange, Layout guides, type .16 left & right, .5 top and bottom, grid guides, 2 columns .19 spacing, 3 rows, zero spacing. -- Mary Sauer http://msauer.mvps.org/ "CCU" <CCU@disc...

Can you move text boxes in slide show view
I'm doing a project and on 2 of the slides I am writing about disposal of waste with gaps where given words can be entered. Is it possible to place these given words in text boxes that the student can choose the correct text box and move and put in the right location in Slide Show view. Only with pretty complex vba cose. There's an example here: http://officeone.mvps.org/vba/mousemove_shape.html -- john ATSIGN PPTAlchemy.co.uk Free PPT Hints, Tips and Tutorials http://www.pptalchemy.co.uk/powerpoint_hints_and_tips_tutorials.html "PP Darlington" ...

Correct formulas in cells, have to retype to work
At work we have a worksheet that was used for all of 2004. Formulas worked fine. At the beginning of the year we made a new worksheet from 2004 and called it 2005. Lately when we put numbers in the cells are not adding up. Example...cell reads =p64+s+64. So the formulas is correct but for some reason it is performing the way it should. If I re-enter the formula then it is fine but I do not want to have to redo all of the page. Any suggestions Is calculation set to manual? Try Tools / options / Calculation - change to automatic Does hitting F2 and then enter on any of these cells...

Cell Linking to Chart Headings
I need to work out how to link part of a chart heading within excel to a cell from another tab within the same excel spreadsheet. The purpose of this is to be able to change one cell in another tab and have it link through to the headings of various charts in other tabs. Can anyone help? You can link a chart title, axis title, data label, or textbox in a chart to a cell. Select the text element in the chart, press the equals key, then select the cell with the mouse. The cell reference appears after the equals sign in the formula bar: =Sheet1!$A$1 The text element in the chart will ...

how can you view cells from different worksheets in to one sheet,.
hi, I have multiple worksheets, more than 200 of them, and i wanted to summarize some cells in to one sheet, I know how to do it by entering the code manually (='7'!$D2), this shows cell d2 in sheet 7, but is there a function to do that like drag or something? You can use this macro myasin http://www.rondebruin.nl/summary.htm -- Regards Ron de Bruin http://www.rondebruin.nl "myasin" <myasin@discussions.microsoft.com> wrote in message news:07759D01-70FE-416A-B54B-009646C2C365@microsoft.com... > hi, > I have multiple worksheets, more than 200 of them, and i...

Excel clicking selects multiple cells instead of a single cell
Excel clicking selects multiple cells instead of a single cell try pressing the F8 key it allows you to select multiple cells and may have been hit by accident "texmaam" wrote: > Excel clicking selects multiple cells instead of a single cell If you're using xl2007, try changing the zoom factor. texmaam wrote: > > Excel clicking selects multiple cells instead of a single cell -- Dave Peterson ...

Add a tick in a cell
Hi all, I want to add a tick sign in a cell, but I don't know where to find a tick sign. Anyone can help ? et "et" <elingtse@rediffmail.com> wrote in news:#z7#YYvsFHA.3852@TK2MSFTNGP15.phx.gbl: > I want to add a tick sign in a cell, but I don't know where to find a > tick sign. You could either use a tich box or a symbol font. Wingdings has one under Alt-0252 -- It is I, DeauDeau (Free after monsieur Leclerc in 'Allo, 'allo) "et" <elingtse@rediffmail.com> wrote in message news:%23z7%23YYvsFHA.3852@TK2MSFTNGP15.phx.gbl... &g...

Cell text formatting
Hi, I am working with cells that are formatted as Text and set to Wrap. However, when I enter text that wraps to multiple lines, and I move focus off of that cell, all that appears there are ##################### characters. If I select the cell again, I can see that the data still exists there. Has anyone seen this? If so, what causes this? And how does one fix this? Thanks in advance, Tom . ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ The # symbol indicat...

Vlookup not working where lookup value contains an apostrophe
Column A contains a list of names including O'Brien. Column B contains the result of a vlookup which uses the names in column A as the look up value. ie Column B2 contains the formula =vlookup(A2 ... The Array table is on a separate sheet within the workbook. All look up values except for O'Brien generate the desired result. O'Brien generates #N/A. Any suggestions as to what is going wrong? It should work fine. Maybe there's a white space somewhere throwing apparent good matches off. Try using TRIM: =VLOOKUP(TRIM(A2),.. Any good? hit the YES -- Max Singapore http://...

Wrapping Text
I have the cell set up but I have too many characters for it to display - does anyone know how many character's you can have in a cell or can you extend how many characters can be displayed in a cell? Hi the maximum is 1,024 characters. BUT you can extend this limit by manually entering linebreaks every 1000 characters with ALT+ENTER. This allows up to 32000 characters in a cell (which is not viewable of course...) >-----Original Message----- >I have the cell set up but I have too many characters for it to display - >does anyone know how many character's you can have ...

Cells(#,#).Value conversion #2
Hey I want to grab a serialized date and turn it into an integer in my vba what is the proper conversion for this ? example: Dim startDate As Integer Dim endDate As Integer startDate = ActiveRow.Cells(1, 2).Value endDate = ActiveRow.Cells(1, 3).Value A date is already an integer, just format it as General. -- HTH RP (remove nothere from the email address if mailing direct) "Alexandre Brisebois (www.pointnetsolutions.com)" <alexandre.brisebois@gmail.com> wrote in message news:1122998952.048872.181990@g44g2000cwa.googlegroups.com... > Hey I want to grab a serialized...

Concatenating non adjacent cells 01-29-10
Hello - I am trying to create a field that concatenates cells that are populated from the previous 12 cells on that row, but excluding blanks and adding a * delimited character between each instance. Please find a 4 column example below ID 1 2 3 4 Result required Z A C D A*C*D Y B C B*C X A B D A*B*D I will be applying this to a 2007 version spreadsheet containing in excess of 10,000 lines. There will be at least 5 blank cells on each row. Many than...

can't edit text in text boxes
what could possibly cause this (I'm out of areas to look) I'm in the process of developing a project and have a main menu form with various controls, text boxes, tabs, etc (the usual stuff) Suddenly (by accident, I notice that I can't edit any of the textboxes... all of the other controls work, just not the text... - the boxes are enabled and allow edits... - the form is enabled... - the form's data source is correct (I think.. the text boxes are filling in with the correct information... - the data source is a 1-record table); - if I open the data table in data sheet view...

Full Text Indexing
I have enabled full text indexing on Exchange 2003 with the purpose of being able to search through all user's email folders for a key word. Even after doing this, it appears that I can only search my own folders. I am doing the search with the Advanced Find in Outlook 2003 and have disabled cached mode (have also tried OWA). I followed the steps for setting up FTI. It has generated the indexes (over 200k). I also set it to allow clients to do searchs on the FTI, but it does not seem to function... at least not how I would expect. 1. Am I wrong in assuming that I should be able to ...

Sequentially Increment a Text Field Based on Select Criteria
I need to automatically assign a sequential number based on the last four positions of a text field on a form based on the maximum value of part of the field +1 and based on the matching the value of the same field to that of a record in another table. In Table Service-Contracts I have a text field titled ARL TRACKING NO. Sample values are as follows: ARL-2009-0001 ARL-2009-0002 ARL-2009-0003 ARL-2010-0001 ARL-2010-0002 ARL-2010-0003 ARL-2010-0004 In Table FISCAL-YEAR I have a field called FY and there is one record which equals “2009”. In the sample above, I expec...

How to format an entire row a certain color depending on the value in a cell?
Howdy, Can anyone of you excel gods help me with the following? I have a small spreedsheet (excel 2003) set up like the following to help me keep track of my music collection: A B C D 1 Title Artist Year Format (1=LP, 2=CD, 3=Tape) 2 HardDaysNight Beatles 1966 1 3 Crossroads Eric Clapton 1985 2 I would like to know at a glance by color coding, what the ratio of LPs,CDs,&Tapes make up my collection. I've figured o...

Copying values into another cell
I have an average knowledge of Excel. I am looking for a formula so I can copy the values in column "a" into column "b". As I enter the value in the first column I want excel to automatically copy it into the second. =if(a1="","",a1) and drag down as far as you need. Bman wrote: > > I have an average knowledge of Excel. I am looking for a formula so I can > copy the values in column "a" into column "b". As I enter the value in the > first column I want excel to automatically copy it into the second. -- Dave P...

How to get the name of worksheet in cell?
Anybody know how to get the name of worksheet in cell? Thanks "A", Try this (in any cell on the worksheet): =MID(CELL("filename",A1), FIND("]", CELL("filename",A1))+1, 255) John "A." <Apiruk.Thunyasathukul@th.yokogawa.com> wrote in message news:f9f101c3be09$47026bf0$a601280a@phx.gbl... > Anybody know how to get the name of worksheet in cell? > Thanks On Mon, 8 Dec 2003 20:02:35 -0800, "A." <Apiruk.Thunyasathukul@th.yokogawa.com> wrote: >Anybody know how to get the name of worksheet in cell? >Thanks O...

color cells when criteria is met
Can i color a cell in Excel when the criteria is met. e.g. when a value is -1 i want to color it red automatically. Cor Format>conditional formatting, cell value is Regards, Peo Sjoblom "Cor" <Cor@discussions.microsoft.com> wrote in message news:50CAC529-95E3-4F76-913F-5A012A602F33@microsoft.com... > Can i color a cell in Excel when the criteria is met. > e.g. when a value is -1 i want to color it red automatically. > > Cor Cor In Excel 97 up, check out conditional formatting Format>Conditional Formatting 'Cell Value is' 'equal to' -...

I don't want my text to wrap
I've got a text box filled with text, and then I want to put a circle over it (filled white, so that the circle blocks out the text behind it, but the text continues uninterrupted behind the circle). For some reason I can't get this to work, even though I've turned off text wrapping on the text box, whenever I put the circle over the text box it still wraps around the contours of the circle. Your statement is a bit fuzzy. Do you want the text to be blocked out so you can't see it? -- Don Vancouver, USA "Craig" <Craig@discussions.microsoft.com> wrote...

Aligning Data Within Merged Cells
I want to merge two columns of data - one of numbers one of text. This is no problem, but I want the text to be aligned in the merged cells, not the numbers. Here is how it appears (hope formatting is OK): 50 XTO 50 XTO 32.5 ZEUS 32.5 ZEUS 417.5 ZEUS 417.5 ZEUS Here is how I would like it: 50 XTO 50 XTO 32.5 ZEUS 32.5 ZEUS 417.5 ZEUS 417.5 ZEUS Any help appreciated, Steve You have to use two columns or manually align each one. "Steve Almond" wrote: > I want to ...

Define cells in Table which do not have dependents.
I have large data table and need to find the cells that do not have dependents. All of the cells must be included in one of the formula from other sheet but it seems that I missed some of the cells. is it possible to find (filter)such cells? ...