Finding Characters

Hi MVP's and other smart people,

I have a column of data (A1 thru A11500) with names or addresses etc. I would like to know if there is a way to display "Delete"
or similar in the adjacent cell in the same row but the next column (B1 thru B11500) where any particular cell in column 'A'
contains certain characters amongst the data within that cell.

I have done this many times using the 'IF' formula when it necessarily matched the entire cell contents but it's only 1 or 2
characters contained within the contents of each cell that I need to match this time.

Hope you understand what I am getting at here :)

All help is appreciated.

Kindest regards
Mike the Kiwi


0
3/1/2004 7:42:17 PM
excel 39879 articles. 2 followers. Follow

2 Replies
514 Views

Similar Articles

[PageSpeed] 20

Hi Mike
if the characters you want to check are always at the same position you
may use the MID function.
e.g. =IF(MID(A1,2,2)="AB","DELETE",A1)

or you may use the FIND function. e.g.
=IF(AND(ISNUMBER(FIND("A",A1)),ISNUMBER(FIND("B",A1))),"DELETE",A1)


--
Regards
Frank Kabel
Frankfurt, Germany

Mike the Kiwi wrote:
> Hi MVP's and other smart people,
>
> I have a column of data (A1 thru A11500) with names or addresses etc.
> I would like to know if there is a way to display "Delete" or similar
> in the adjacent cell in the same row but the next column (B1 thru
> B11500) where any particular cell in column 'A' contains certain
> characters amongst the data within that cell.
>
> I have done this many times using the 'IF' formula when it
> necessarily matched the entire cell contents but it's only 1 or 2
> characters contained within the contents of each cell that I need to
> match this time.
>
> Hope you understand what I am getting at here :)
>
> All help is appreciated.
>
> Kindest regards
> Mike the Kiwi

0
frank.kabel (11126)
3/1/2004 7:55:36 PM
In B1, put

=IF(COUNTIF(A1,"*bcd*")>0,"DELETE","")

and copy down

-- 
Regards
           Ken.......................    Microsoft MVP - Excel
                  Sys Spec - Win XP Pro /  XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



"Mike the Kiwi" <kiwiislander7@hotmail.com> wrote in message
news:1078170136.965273@kyle.snap.net.nz...
> Hi MVP's and other smart people,
>
> I have a column of data (A1 thru A11500) with names or addresses etc. I would
like to know if there is a way to display "Delete"
> or similar in the adjacent cell in the same row but the next column (B1 thru
B11500) where any particular cell in column 'A'
> contains certain characters amongst the data within that cell.
>
> I have done this many times using the 'IF' formula when it necessarily matched
the entire cell contents but it's only 1 or 2
> characters contained within the contents of each cell that I need to match
this time.
>
> Hope you understand what I am getting at here :)
>
> All help is appreciated.
>
> Kindest regards
> Mike the Kiwi
>
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.605 / Virus Database: 385 - Release Date: 01/03/2004


0
ken.wright (2489)
3/1/2004 9:41:31 PM
Reply:

Similar Artilces:

Combo box to find records
Hi, need help with what I am sure is a simple problem. I have a form and wish to find records using a combo box. Which I know how to do. The problem is it takes me to "one" record. I would like to select, for example, employee name: John Smith and it return all records for John Smith while filtering out all other records. I would be thankful for any help. Thanks, Michael On Wed, 30 Jan 2008 20:24:04 -0800, Michaelchessking <Michaelchessking@discussions.microsoft.com> wrote: >Hi, need help with what I am sure is a simple problem. > >I have a form and wis...

find location max value in column
I need to find the cell location of the max value in a column. If paste the following [from =(cell onwards] into a cell I get the righ result. Having a hgard time getting the right VBA code to make thi work in an unattended macro. Probably some bonehead mistake on my part but I cannot find it. Suggestions? GOAL is to make this formula work. Range("z2").Formula "=(CELL(e1,OFFSET(e1,MATCH(MAX(e1:e100),e1:100,0)-1,0)))" TIA, Ro -- rroac ----------------------------------------------------------------------- rroach's Profile: http://www.excelforum.com/member.php?act...

Finding last occurence of Interior.ColorIndex 36
I have been searching for a formula to help me find the last time (most recent) color 36 appears in a column of colored cells. Most of the time there is no typed information and when there is, it is not the same for every color 36 cell. The cells are not conditionally formatted. C. Pearson's site is great, but I can't find what I need. I don't want to change color, I don't want to know how many times it shows up, I just want to find the last time it is in the column. I could count down to find it, but there are over 15,000 columns spread over several worksheet...

where can i find a list of excell formula's? #2
I'm learning to use excel more and more and i would like to know how to create a formula that subtracts one cell from another. Is there a list of formula's for excel? Look in HELP contents for function index -- Don Guillett SalesAid Software donaldb@281.com "Art" <Art@discussions.microsoft.com> wrote in message news:DFF58A3A-86CE-415C-A471-0F995F95C108@microsoft.com... > I'm learning to use excel more and more and i would like to know how to > create a formula that subtracts one cell from another. Is there a list of > formula's for excel? Art, y...

upgrade to 2007 can't find exsiting website
I just upgrade from microsoft office 2003 to 2007 office enterprise, my operating system is visita. I'm trying to get my website from 2003, it comes in but none of my pictures,background,pages. doesn't show. I've uninstall the software and installe it again, hopeing this would fix the problem. I'm at a lost here. someone please help. I think Microsoft should let office 2003 run with visita, instead running out to buy some software that suppose to be compatiable. this is a waste of money. Office 2003 SP2 & SP3 will run on Vista. -- ~~~~~~~~~~~~~~~~~~ Rob Giorda...

Find 3rd Record
Hi there, I need to find the third record in a query. This is my basic select query: SELECT tblGuests.GID, tblGuests.Date FROM tblGuests ORDER BY tblGuests.GID, tblGuests.Date; There are several thousand records in tblGuests, some Guests (GID) have just one record, others have 20 or more. I've thought of DateDiff function but that doesn't seem to work. I have also thought that I need to find only records where there are more than 2 records for a certain GID so I can filter out the Guests who have only visited 1 or 2 times using the count function but have no idea how to do t...

characters automatically converted to arabic fonts
Hi All. I am trying to input some information for some of my clients in an Excel Sheet. However in one column i have to type data in the following format : A5/69. When i type it , the characters are to converted to some arabic-like fonts. Is there any settings which i must adjust to prevent this. Thks in anticipation. Masoud Enter a single apostrophe in front of the data -- Gary's Student "masoud" wrote: > Hi All. > I am trying to input some information for some of my clients in an Excel > Sheet. However in one column i have to type data in the following for...

How to find the datatype of a particular variable...
Dear pals, I am developing an win32 console application, I need to find the data type of particular variable dynamically. Is it possible? Say for eg, declaring 'a' as integer, how can I find the data type of variable 'a' dynamically. Awaiting for your ideas. note: please ignore this, if it is not a relevant group. @Shahul. I'm trying to imagine why your would need this. It sounds like this will be hard coded so you'll know. If you need something higher-level to do this, I would probably create a class that is similar to a variant in that it could store any t...

Able to find text properties?
Is there a function in excel to test the text properties of a cell. ie. if it's bold or red color text? thanks naveed010@hotmail.com G0 to the Help files, and lookup Cell worksheet function That will give you a list of the types of formats that can be returned. Mostly date, time and currency formats. The only color return would be the "negative red" in currency. What you're looking for needs code. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ========================================...

tool to find GDI resource leak
Hallo, i search a tool or DLL or source to find GDI resource leaks. I've Boundschecker, but it don't find GDI resource leaks. Jimmy > i search a tool or DLL or source to find GDI resource leaks. > I've Boundschecker, but it don't find GDI resource leaks. Hmm, as far as I can remember BC did find GDI leaks without problems several years ago, when I was using it. You may also check this: http://msdn.microsoft.com/msdnmag/issues/03/01/GDILeaks/default.aspx -- Igor Green, Grig Software. www.grigsoft.com Compare It! + Synchronize It! : files and folders comparison neve...

character attributes lost (eg font, italics, bold) when cutting/pasting from excel to quark
hi all, I can cut/paste from excel to quark, but I lose the character attributes (eg font, italics/bold) in the cut/paste, though the text is pasted. I'm new to this so I don't know whether this is a quark limitation, or limitation of windows cut amd paste, or my inexperience. thanks in advance. What is a QUARK??? Isn't that a star that exploded in space 1 billion years ago? >-----Original Message----- >hi all, > >I can cut/paste from excel to quark, but I lose the character attributes >(eg font, italics/bold) in the cut/paste, though the text is pasted. ...

Find Last instance of carriage return In a Memo Field
Hi, I have a form where the user usually pastes a glob of text into it a memo field called "Comment". After he pastes the text, the cursor is obviously at the end of the text. I would like the user to be able to click a button and for the comment to be highlighted from the very end of the text to the first instance of a carriage return going backwards. In other words, I want to automatically select the very last line of the comment. I was thinking that I could use seldown, but I would need to first find the Last instance of Chr$(13). Or, can the instr function can be ...

where can I find an excel file I "saved as" in OLKE9?
I saved over an EXCELL file that someone sent to me in OUTLOOK, after adding hours of data. Unfortunately I clicked "save as" but did not change the directory. It has saved my EXCEL FILE in a folder called OLKE9. I have searchjed the path, but cant fnd the file (I am displaying hidden files). Please help, I need this file in only 8 hours time or my life will be a misery. Easy way: Open ANY file (EXCEPT THAT ONE!!) through Outlook, and hit File-->Save as. That'll take you to the OLK folder. If you don't see it, likely, it's gone. Never, ever open files attached ...

Insert character in a cell using a keyboard shortcut
Hi, I have a workbook with values in Column A that have to be split into columns. Before splitting into columns I need to manually insert delimiters eg. "~" . The user will get into the cell in Edit mode and then use a keyboard shortcut to insert the pre-defined delimiter viz. "~". Can this be done? (I tried recording a macro but the entire cell string with the delimter was recorded.) If this is not possible, any other way/ideas for inserting delimiters where there is no pattern for programatically inserting the same? Thanks in advance for the help. Regard...

How To Find A Macro -- Word 2007
Our company has several hundred controlled forms that are saved as protected templates on a server. Recently, we've had many cases of people trying to open these templates and getting error messages about macros (Do you want to enable?) even though there shouldn't be any macros in the forms that are giving the errors. I know very little (read "less than nothing") about macros. How do you find and disable an unwanted macro in a document? Where do they hide? I don't know if it matters, but all of the forms that give these errors have been recently revised...

Finding Macros In Workbooks
Is there a wasy to find all Excel workbooks in a folder that have a macro in them. If so, how? Thanks, John ...

Extending XmlDocument and associated classes to provide character positions.
OK here's is what I wish to do. I have an XML file that I want to read into an XmlDocument. I then want to be able to interrogate the XmlNodes to find both their start AND end character positions within the original file. So e.g. <tagA><tagB>sometext</tagB></tagA> ^ ^ ^ ^ ^ ^ 0 6 12 19 26 33 tagA: start=0, end=33 tagB: start=6, end=26 sometext: start=12, end=19 I have seen the LineInfo example within the .net docs, see: "Extending the DOM" ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/cpconextendingdom.htm and ...

Find oldest warranty ExpDate(s) for each customer
I have a tblWarranties containing multiple entries for each customer. I would like to return a record set of only the oldest ExpDate(s) for each customer. It is possible that a customer would have multiple warranties with the same ExpDate, so I would want all. So it would need to ignore the customers warranty records with ExpDates older then the most current ExpDate. For instance, in the example below only the last two entries would be part of the record set. WarrantyType Term BeginDate ExpDate Original Warranty 2 09-01-01 09-30-03 Parts...

Finding last row in a column
Column A contains entries of any formats including blanks. My objective is to find the last blank row# in Column A, and post it in cell D1. Can you help me with a formula to do that? Thanks I expect you haven't described your question correctly. The last blank row in column A is most likely the last row on the worksheet, row 65536. Do you mean the last *embedded* blank row? On Sun, 15 Aug 2004 02:33:41 GMT, "daniel chen" <danchen@worldnet.att.net> wrote: >Column A contains entries of any formats including blanks. >My objective is to find the last blank row# in C...

Excel (Office 2007 B2TR) -- Password Protecting A Sheet -- Character Limit ?
I have used really long passwords in Excel (Office 2007 BETA). My passwords are longer than 32 characters but not more than 64 characters. Two issues with B2TR seem to have occurred: 1.) password length now limited? 2.) cannot paste password into 'Confirm Password' dialog? Here's how I have been protecting Sheets (not Workbook): With the sheet I wish to protect in front and visible, I select 'Review' Then I select 'Protect Sheet' 'Protect Sheet' dialog appears. I can paste my really long password. Click [OK]. 'Confirm Password' dialog appear...

count occurences of a character in a colume
Im sure there is an easy way, I cant find it though. How could I count all cells in a colume where the value was X this is excel 2007 thanks Craig =countif(a:a,"x") is one way. Debra Dalgleish has a bunch of "counting" function descriptions here: http://contextures.com/xlFunctions04.html And a bunch more notes for "sum" functions here: http://contextures.com/xlFunctions01.html MSNews wrote: > > Im sure there is an easy way, I cant find it though. > How could I count all cells in a colume where the value was X > this is excel 2007 > > t...

Custom list limited to 255 characters? (XL 2007)
I have a client who claims that she could create longer custom lists in Excel 2003 compared to the limit of 255 characters that she managed to Exceed when trying to regain her customizations in the newly installed Office. It seems as if she is right? Anyone else who had this problem or can point me towards a better solution? -- Best regards Malin D Office consultant/Trainer EXCEL 2007 Hit F1 to launch the F1 Help Facility Type in:- EXCEL Specifications And Limits - in the Search box then hit Search. Inspect the topic. Ctrl-F to lauch Find then, in the find...

find replace cursor default to find box
When using replace in Excel, after the first usage, the cursor defaults to showing in the replace box. This is different to previous versions of Excel and to other office applications including the VBA editor. I'd like it so the cursor always defaulted to the find box. ...

updates
I have successfully updated Office 2004 through 10.9.1, but when I try to go any further I get a message - "installer cannot find the correct software . . . ." How did you update? Update 10.1.9 is for Office v.X only; the latest update for Office 2004 is 11.3.4. If you are using Office 2004, simply run Microsoft AutoUpdate (which can be found in your Applications folder) and let it perform all the updates for you. On 12/3/07 20:04, in article 1173726294.060440.242930@n33g2000cwc.googlegroups.com, "skrouse@verizon.net" <skrouse@verizon.net> wrote: > I have succ...

Funny Characters on emails
I faced the same problem last year but renstalled Exchange=20 5.5 from square one. It's again here with me. Isn't there=20 a solution? Well, When I send out emails, the recipient=20 receives Garbage! Funny characters at the end of exchange=20 or at the Beginning! Most of them talk about mime setup=20 and unsupported Mime format! Please see below What I'm=20 talking about! I have applied and reapplied the service=20 packs in vain! Last year, I was running exchange 5.5 on=20 Windows NT 4 Server now I decided to push it to Windows=20 2000 server since my friends told me my NT server&...