How to use Find (Ctrl-F) to find non-alpha

Is there some way to search a sheet for anything BUT a-z or A-Z?

tx ;-)
0
hsmills (2)
10/22/2010 9:31:05 PM
excel 39879 articles. 2 followers. Follow

4 Replies
422 Views

Similar Articles

[PageSpeed] 41

Finding numerals or a mixture of numbers and letters (and other characters) 
works ok for me.

What are you having trouble with?



On 10/22/2010 16:31, Heather Mills wrote:
> Is there some way to search a sheet for anything BUT a-z or A-Z?
>
> tx ;-)

-- 
Dave Peterson
0
petersod1 (224)
10/22/2010 10:54:28 PM
On Fri, 22 Oct 2010 17:54:28 -0500, Dave Peterson
<petersod@XSPAMverizon.net> wrote:

>Finding numerals or a mixture of numbers and letters (and other characters) 
>works ok for me.
>
>What are you having trouble with?
>
>
>
>On 10/22/2010 16:31, Heather Mills wrote:
>> Is there some way to search a sheet for anything BUT a-z or A-Z?
>>
>> tx ;-)

sorry, not clr. I want to search a col for cells containing any
non-alpha characters. Most of the cells contain only alpha. A few have
some misc chars like "\", "'", "_", etc. 

in this list, I want a search string that'll find 3, 5, & 7

1  abc
2  xyz
3  p_s
4  mmm
5  ab\
6  COD
7  don't
0
hsmills (2)
10/23/2010 12:05:54 AM
Hello,

I don't know how to use Find (Ctrl-F) to find non-alpha.

If your data are in column A beginning at A1, you can try this array 
formula:
Put in B1 this following array formula:
=(SUM(--(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))<65)) +
SUM( --(CODE(UPPER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))>90)))>0

This formula must be entered with the combination of the three keys
Ctrl+Shift+Enter instead with the single key Enter. Then drag
down the formula.

This formula should display the value TRUE if a non letter is in the string.



"Heather Mills"  a �crit dans le message de groupe de discussion : 
ci04c61sl8mabivemld8v8mdoq1p6jdg1u@4ax.com...

Is there some way to search a sheet for anything BUT a-z or A-Z?

tx ;-) 

0
Please9856 (13)
10/23/2010 4:20:01 AM
Excel's find doesn't support this kind of search.

About the only wildcards that excel's find can use are from the old DOS days:
* (any characters)
? (any single character)

Obviously, you can use multiple Find's to search for as many characters as you want.


On 10/22/2010 19:05, Heather Mills wrote:
> On Fri, 22 Oct 2010 17:54:28 -0500, Dave Peterson
> <petersod@XSPAMverizon.net>  wrote:
>
>> Finding numerals or a mixture of numbers and letters (and other characters)
>> works ok for me.
>>
>> What are you having trouble with?
>>
>>
>>
>> On 10/22/2010 16:31, Heather Mills wrote:
>>> Is there some way to search a sheet for anything BUT a-z or A-Z?
>>>
>>> tx ;-)
>
> sorry, not clr. I want to search a col for cells containing any
> non-alpha characters. Most of the cells contain only alpha. A few have
> some misc chars like "\", "'", "_", etc.
>
> in this list, I want a search string that'll find 3, 5,&  7
>
> 1  abc
> 2  xyz
> 3  p_s
> 4  mmm
> 5  ab\
> 6  COD
> 7  don't

-- 
Dave Peterson
0
petersod1 (224)
10/23/2010 12:03:47 PM
Reply:

Similar Artilces:

Using Autofilter on a protected sheet
When I protect a worksheet in Excel 2000 (with SP-3 loaded) the Autofilter function doesn't work. The arrows are visibile at the top of the selected cells, but I can't open the drop-down lists. The Autofilter has been created on a range of cells that are NOT locked. Does anybody know if there is a way around this problem? If you protect the worksheet programmatically, you can use the AutoFilter. There are instructions here: http://www.contextures.com/xlautofilter03.html#Protect John wrote: > When I protect a worksheet in Excel 2000 (with SP-3 > loaded) the Autofil...

Outlook 2002-Csnt use my address book
When I try to use my reinstalled version of Outlook 203 I get the following error concerning my CONTACTS FOLDER... "The address book could not be displayed. The Contents folder associated with the address list could not be opened; it may have been moved or deleted, or you do not have permissions." I have copied my old mail from a previous installation of Outlook 2002 to this version, and now get this error. I cannot delete any of the items in the address list. I am unsure of what caused this or how to fix it. I also would NOT like to loose all my addrees. PLEASE HELP. DavidF...

Re: Find last cell in a column, Delete its contents and make it ac
Sub uselastcellinL() Application.Goto Cells(Rows.Count, "L").End(xlUp) ActiveCell.ClearContents End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Don Guillett" <dguillett1@gmail.com> wrote in message news:... > Sub deletelastcellinL() > Cells(Rows.Count, "L").End(xlUp).Delete shift:=xlUp > End Sub > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett@gmail.com > "George" <George@discussions.microsoft.com> wrote in message > news:D...

File in Use problem
I'm revisiting some VBS scripts that I wrote several years ago to generate resumes for me (via Word macros) and would like some guidance on a couple of errors I am getting. I'll post about the two errors separately so that each has its own thread. My VBS script is pretty simple: ======================= ' Get arguments into variables If WScript.Arguments.Count > 0 Then MsgBox "Too many arguments; expecting none." WScript.Quit End If ' Find path for MyDocuments folder Set objShell = CreateObject("Shell.Application") Set objFolder = o...

help with quote program using muliple prices for one quantity {cross post}
Okay, Forgive me ahead of time, I really don't know much about Excel. I can work with templates and create simple formulas but alot of what I read here comes across as "Excelspeak" to me. Here is what I want to do: I have a price list sheet created. This price list has cells labled by name and then "duration", basically for every item there are four prices, based on duration of a rental or sale. I want to create a quote program that will pull data from the price list based on the quantities entered in the quote program. For example Price list is "System A, des...

Named range(s) of non-adjacent cells return #VALUE! error in array formulas
I have ranges made up of non-adjacent cells from one worksheet and I get a #VALUE! error when I try to do any conditional count or sum calculation with an array formula on either or both of them. Each range is a selection of 32 non-adjacent cells from a single column. The cells contain array formulas that return percentages. Example of range values I8: 5.9% I11: 12.1% I14: 22.3% I17: 0.0% I was able to get values returned from simple functions like Max and Min but the following example gives me the #VALUE! error: {=SUM(IF((Rng>0),1,0))} =COUNTIF(Rng,"<.0125") Any wisdom ...

Can the isv.config be used to launch Workflow rules
We use several workflow rules and would like to customize the menu bar to include a button which can quickly launch certain workflow rules. Is it possible to do this by editting the isv.config file? And how? it seems to be possible as you can of course go to actions then run a workflow so one way might be to find the source code that does this. Another way might be to have the menu item or button call an external asp.net page which calls back into the platform and runs the workflow. I have not tried this or heard of anyone doing this so have no idea if its possible. -- John O'Donnell ...

Cannot find file error
HI, I have a machine that comes up with an error when "double-clicking" to open a file. When I double click any excel file I get this error. Cannot find file "..Filename..." (or one of its components). Make sure the path and filename are correct and that all required libraries are available. The file can be on a file server or on local disk and I still get this error. I tried installing Office 2k SP3 in hopes that would fix something but, that hasn't worked. any hints? thanx, In Excel Go to Tools Options General and uncheck Ignore Other Applications ...

Trouble forwarding email as text message using SMS
I have set up to forward specific emails to my cell phone as text messages. The content of the message does not come thru as part of the text (these messages are SHORT messages) Any suggestions? -- Sheri Sheri <Sheri@discussions.microsoft.com> wrote: > I have set up to forward specific emails to my cell phone as text > messages. The content of the message does not come thru as part of > the text (these messages are SHORT messages) > Any suggestions? No need to ask more than once. -- Brian Tillman [MVP-Outlook] ...

What template do I use for labels that are 33/sheet (1x2 13/16) ?
I accidently bought the 33 labels per sheet rather than the 30 labels per sheet. Now I'm stuck with these labels and am looking for a template to use in Publisher (or other office product) that will allow my HP LaserJet 5L printer to use the 33 per sheet labels. Any help is appreciated. Does this sheet of labels have margins? About the only way I can get your dimensions to print 33 labels is not to have margins. Letter paper is 11 inches, so there is no room for a top and bottom margin, 3 times 2.8125 = 8.4375 If I were to setup this scenario, I would type your dimensions in page ...

How to start the default mail program using Excel VBA? #2
From Excel (using VBA) I want to click a button and start the default mail program installed on the computer. After this I want to prepopulate the TO line in Outlook Express or Outlook (depending on what's installed on the machine) with a few e-mail addresses (stored in my spreadsheet). Any sample code for this please? Thanks in advance Michalakis Michael michalakis_michael@hotmail.com ...

Using headset to call outlook contact
I have a compaq pc running xp and office xp pro. I what to be able to make phone calls to contacts in outlook by using my modem and the phone line contected to it. I also have a headset pluged into my sound card. How do I do this? I currently can get it to dial the number but i dont hear anything in the headset. IIRC, you will need to pick up the actual telephone handset to make calls. Talkworks pro (discontinued from Symantec) used to handle this by default. You might want to google it to see if there is any substitute application. -- Milly Staples [MVP - Outlook] Post all replies to...

How to use a ListCtrl in a doc/view application?
I have build a application with a CFormView with a ListControl on the dialog resource. I want to know where you are suppose to declare the variable that is used to reference that listcontrol. I Just used the class wizard to add a reference to it in my view class, but how to I access that variable in my document class. I want to say update my list from within the doc class, How do I do that? Hope this all makes sense. >I have build a application with a CFormView with a ListControl on the dialog >resource. I want to know where you are suppose to declare the variable that >is used to...

Word in Office 2000 will not shut down properly when using Windows
I recently upgraded my computer running Windows XP to one operating Windows 7. I had no problem installing Office 2000. It works fine, but Word will not shut down properly when I close the program. I get a dialogue box from Windows saying the program is no longer responding and Windows is searching for a solution. After Windows conducts its search, another dialogue box comes up with a "close program" button. I click that and then it shuts downs. Any help would be appreciated as I do not want to shell out $$$ to update Office 2000 when it otherwise works fine, except for...

Journal Entry Number collision using EConnect auto-numbered docs
We encountered a recent EConnect exception that is of concern. We have an interface that uses the .NET libraries to publish a single journal entry (with several hundred distribution lines). The interface has been in production for several months. In a recent run of the interface, we received an error messaging indicating the entry didn’t balance, when in fact its entries did balance. Further investigation revealed the distribution lines (originating from EConnect) merged with another GL journal entry that was created by the system from the posting of a receiving batch in the...

Finding the new Record
I need my new records, once entered, to slot into their alphabetical position in the form, not at the END of the form/table. If I enter a record and then "Me.Requery", the record pointer goes to the first record, not the record I just entered. I've been struggling with me.recrodsetclone.bookmark, but results are inconsistent - it works if I click my save button, but NOT if I click on the pencil icon on the top right of the form. TABLE with 3 fields RecNo (autonumber) fname (text) sname (text) ENTRY FORM form sorted in order of Surname RecNo (visible but not enabled) Fi...

Preventing certail domains from using * Address Space?
Due to an integration we need to ensure that ALL SMTP traffic to certain domains stays on our internal network tunnels and is delivered to a specific IP address in a separate Exchange Org. The problem is that even with a separate SMTP Connector with a cost of 1 we are still seeing some mail flow to our other higher cost connectors. How can this be prevented? Thank you for your help, Paul <pfortner@gmail.com> wrote in message news:1127494597.758768.269880@g14g2000cwa.googlegroups.com... > Due to an integration we need to ensure that ALL SMTP traffic to > certain domains stays ...

Using Windows Service and CRM
Happy Holidays, All! This isn't covered in the SDK, that I can see, but we have a service running that needs to query the CRM database (using the SDK) - but it is running on an application server out side of CRM. We can do this with impersonation, using an existing user in CRM - but the password needs to be hardcoded that way - making it necessary to revisit the applicatin every 90 days... Any other thoughts on how to run a windows service against CRM? Thanks! Kristina Kristina, Ideally (and technically, legally), the service sould have it's own ID. What we've done though i...

Anyone using MICR and ECC/ECA with RMS?
Hi, our business wants to start using Electronic Check Acceptance / Electronic Check Conversion, but I've not come across any merchant services or providers that have a product which is compatable and integrates with RMS. Anyone know of a service provider or bank which does? If not, is anyone even using ECC/ECA, and how do you manually integrate it into your business process operations? Thanx! The only RMS Integration with check services is through PC Charge. As far as I know, the only thing you can really do with PCCharge is Check Verification, where you still have to physical...

Use Query or Macro to Creat enew Database
I need to figure out a way to create a new database from within an existing database during a process in which i am running several queries. the new database name will have a generic name like "Quarterly..." but will then be concatenated with the quarterly submittal date in a specific format (e.g., 20070601) I'm thinking i could create a query to determine the quarterly date and then concatenate with the other part of the file name, but how would i actually create a process for creating a new database (in a specified directory) from within my existing directory. any sugg...

Non-Breaking Space + Print Issue
I have two questions for the group. 1) I'm having the same problem as others with getting the Y axis labels to print completely. It appears that the last character of the axis label is cut in half. I've seen someone post the code for a non-breaking space, but I can't find it. 2) I have some bubble charts that I'm trying to print. When I print them directly from excel, they print as squares with inscribed circles. The same happens when I print from Word and the chart is copies as a picture file from Excel to Word. Once the Word document is PDF'd, the bubble cha...

How to fix keys t s h c v non-responsive only in Excel?
When I press keys t s h c and v in Excel 2003, nothing happens. If I use the Shift key, then the keys work, but only as caps. The keys work fine in other Office applications, and worked fine in Excel 2003 until this morning. bareedy, Interesting. Try the stuff at http://www.smokeylake.com/excel/vfaq.htm, "Excel acts generally goofy." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "bareedy" <bareedy@discussions.microsoft.com> wrote in message news:E613210C-9895-4DDD-906D-219336F7CDD8@microsoft.com... > When I press ke...

Using Java Applets in CHtmlView
I noticed that even when I have newest Java 1.4 installed and IE supports Applets compiled with it, CHtmlView only shows Applets compiled with Java 1.1. I assume this is because CHtmlView uses Microsoft Java Virtual Machine ( MSJVM ). Is there any way to tell CHtmlView to use same Java version that the IE is using? Aggro wrote: > I noticed that even when I have newest Java 1.4 installed and IE > supports Applets compiled with it, CHtmlView only shows Applets compiled > with Java 1.1. I assume this is because CHtmlView uses Microsoft Java > Virtual Machine ( MSJVM ). > ...

Read Text File into Excel Using VBA
Hi All, I'm a new VBA programmer. I know how to pull an entire text file into an Excel Spreadsheet, but I only want specific information from the text file not the entire text file. What I have is about 25 text files stored in a folder, let's say C:\test. Each file is named by a property address as follows: 209 MAIN ST.txt 213 MAIN ST.txt 111 ELM ST.txt 2356 WOOD AVE.txt On the 11th row of each file is as follows: Property Address:209 MAIN ST On the 31st row of each file is as follows: Total Value:30500 What I would like to do is read each file located in the "C:\test folder...

Print only the used sheets in all open files
I have the below macro to print the sheets that have info in cells D6 or D3 for all open workbooks. "PrintAllOpenFiles.xlsm" is the file in the xlstartup dir. If u run the small macro listed first within a single file it works but inside the bigger macro it doesn't. It doesn't look like anything happens - no debug option or anything. Any help is appreciated. 'Print Used Sheets Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.Visible = -1 Then If sh.Range("D6").Value <> &...