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
449 Views

Similar Articles

[PageSpeed] 57

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:

finding specific day in range
I am OK with some Adv Beg excel techniques. But I am not sure how to get the next step done. I do a monthly schedule in grid format in excel, names down the left (A-column), and Dates across the top(row-2). I have set up a template to auto fill the date each time I enter the new date for the month in the cell A1 range "DAY" is C2 to AG2. This starts at C2 with "=A1" then in C3 "=(c2+1)". This is formated to show text days of the week "ddd". In the lower rows for each person a letter is used to indicate where they are to work for this day. Some letters ar...

Ctrl+Shift+M does not work in Contact Notes section
Ctrl+Shift+M will work when I first open a Contact, but as soon as I click in the Notes box, it doesn't work anymore. Is there a different keyboard shortcut I can use to open a completely blank email message? You'll need to click on another field in the contact or off the contact for it to work. I'm not sure why it's not working in the notes field, unless its something to do with the underlying word editor. (It doesn't work in either 2007 or 2010 in the notes field of any form.) you can also click the email button to open a new message - delete the address...

Can you use a cell reference in a chart?
I have a dilemna in trying to chart some data. Wondered if anyone has run across this before. Have data with a space instead of a comma, IE 1 555.55 I was able to add the comma by using a CLEAN and SUBSTITUTE statement in a different column, number now displays correctly IE 1,555.55 Cannot graph, and assume that is because the cell is actually a formula rather than a number. So I tried using a cell reference IE "=B22" in another column, and graphing off of that column, but still displays the same "0". Formatting the column to a number format does not seem to help. ...

Using 2 vouchers on 1 transaction
An issue has just appeared where somebody has accumulated 2 different gift vouchers and would like to redeem them on the same transaction. What would be the best way to handle this request? Any help is appreciated and I thank you in advance. JN In Manager | Database | Tender Types | select your Gift Card tender and check "Allow multiple entries". You will need to run a Z at POS before the change will be in effect. This will give you as many Gift card entries as you need on the tender screen. You can do this for all tender types if needed. Craig "Portions" <Por...

how do i find values on a line graph in Excel?
i made a plot graph connected by a line. now i need to find values on that line. how? thanks. Two possible ways: 1. if you have the formula for the line, use it go calculate the reading. 2. If you have sample points on the line, but no formula, then you can use the FORECAST() function. If you have two points [x1,y1] and [x2,y2] and you also have a third x3, then FORECAST() will calculate y3 for you. The neat thing about FORECAST() is that it works equally well if x3 is between x1 and x2 or is outside of the range. -- Gary''s Student "sorrelponie" wrote: > i...

About using CDO to send email within Excel
Hello Excel Experts: Urgent help is required from you IT guys. I need to send emails silently within Excel using VBA, and the following code doesn't work for me (the code is found from PaulSadowski.com): Sub Mail_Small_Text_CDO() Dim iMsg As Object Dim iConf As Object Dim strbody As String Dim Flds As Variant Set iMsg = CreateObject("CDO.Message") Set iConf = CreateObject("CDO.Configuration") iConf.Load -1 ' CDO Source Defaults Set Flds = iConf.Fields With Flds .Item("http://schemas.microsoft.com/cdo/conf...

Update CRM Data using vb.net
Hi, I am new to CRM. I am trying to retrive and update information into CRM using my VB.Net application. I am able to retrieve information from CRM. But i am not able to update. Can any one tell me how to update contact information using VB.Net. If any sample program it will help me to create some applications. Thanks in advance. Phani Check out the Microsoft CRM examples on msdn.microsoft.com - a number of these show updfating data "Phani" <phanimadhavsv@hotmail.com> wrote in message news:Ow5xjhnWDHA.2064@TK2MSFTNGP11.phx.gbl... > Hi, > > I am new to CRM. I am...

Using multiple pc's with email account
Hi, Has anyone got help for the following: I would like to setup 3 computers with outlook, using the same email account. If someone send us a email, it must show on all 3 computers...ok that is the easy part, got that figured out. Now say one of the users reply to a email, is there a way of letting the other 2 outlook users know that that specific email has been answered/read? Ok, so just to clarify, the idea is that if one user replied to an certain mail, the other users must know that they don't have to reply. Using outlook 2007, Win XP. thanks for any help! -- Derick "Deri...

How do I use the thumbnail view to re-order pages in a doc file?
PDF file pages in most readers can be re-ordered using the thumbnail layout by simple dragging up or down. I cannot figure out how this might be done in MSWord's thumbnail view equivalent with doc files, nor does there seem to be any information available in Help. Can this be done at all - and if so, how? Word doesn't really have "pages." One thing you could do is start each page with a heading set to "page break before," then go to Outline View, collapse the headings to that level, and drag the visible headings up or down. On Jun 5, 5:47=A0pm, sgodl...

Remove blank entries from a list box either using VBA or basic formula #2
Can anyone help with this? Celtic_Avenger Wrote: > Hi. > > I've got a spead sheet that asks the user to enter details of worker > onto one sheet. > > ie. Surname.............First Name............Age.........etc.etc. > > This data is then used in a few pivot table reports. > > One pivot table simply lists the workers full names for entry int > another sheer. > > The cells asking for the name are have a validation setting to > listbox. > > The only problem I have is that the list box range is predetermined > but the list may be fro...

Where can I find a tutorial for using Outlook 2002?
I'm having trouble finding my way around this program. When I type it's invisible until I choose a color for the font. When I press enter it gives me a double space when I want single space. Where can I find an overview of how to use this program. Thanks for any help you'll find information at outlook-tips.net and slipstick.com but the problem you are experiences is because you are using HTML messages and have the font colors wrong. Look in tools, options, mail format, Fonts and also in Stationery to see if the font color is set to white. For single spacing, use Shift+Enter...

anyone else seen problem using LEN() function in Runtime?
HI. I have an applicaiton that for one of the combo boxes has a query that uses the Len() function to shorten the description length for sorting the list. Eg: Right([Partdescription],(Len([PartDescription])-11)) This was working fine - however having loaded the AccessRT.msp Runtime pre-release patch from microsoft to fix the SaveAsPDF addin problem the len function doesnt work. I have even tried to generate a standalone app that has a Form with a button and the VB code has a Msgbox Len([Me]![ErrorBox]) Under the runtime with the pre-release patch the msgbox does not display. Under ...

Finding Data in multiple worksheets
Using Excel 2000, how can I find data in multiple worksheets; example: any number between 200 & 299 in 32 worksheets? If you mean you want to find a single number across 32 worksheets, group your worksheets (click on the first, ctrl-click on subsequent (or even shift-click)). Then Edit|Find But I think I'd use Jan Karel Pieterse's FlexFind: http://www.oaltd.co.uk/MVP/ If you meant you wanted to find all 100 numbers (200-299) in all 32 sheets, then I think I'd have a macro search for each number and report the results on a new worksheet. "mark@southwestconst.com&quo...

Any way to find a window(Control) is hidden/Overlapped by other one?
I'm working with MFC Application which contains combo boxes and static text controls. ToolTip is given to static text to expose full text due to space constraints. Below the ComboBox i have static text which pop-ups ToolTip when mouse hovered on it. When i get my ComboBox's drop-down window, it's overlapping on the static text control. If you move your mouse from one item to other item in the ComboBox then i'm getting static text's ToolTip blocking the ComboBox item names. Is there any way to find out that one window(Static text)is overlapped by other(ComboBox) to fix thi...

Using c# object in Excel VBA
Hi All I have writen an COM Object in C# to use it in Excel. This Object has a Function that delivers a List of Users from a Database. My problem is now that I get the List of Users but it is not posible to get one of the Properties of the User. But when I create a new Object with Set user = CreateObject("GuptaIntferface.UserVOCom") I can see all Properties of this object. My Question is now how can I tell VBA now that the objects in the List are the UserVOCOM Objects? thx for help Michael I'm kind of stabbing the dark on this one as I've never worked with a C...

Formula
Hi there I’d first like to start out by thanking anyone who can help me out with this issue but that, for many of my own reasons which are very valid, I cannot use a VBA solution – only a formula based solution. I have seven columns of data. Most entries overlap throughout the columns but I need to find every unique entry in all of these columns. My results need to be shown in one neat column with no spaces in between entries. I have vastly simplified the amount of data for this example – this is usually over 500 lines for each column and I have about 40 columns, some of the column...

IIF statement using 2 criteria
I have an attendance database in access 2007 I am assigning points based on absence I have a field called classattending which returns a value of class 1, class 2, or class 3 the classes are of varying lengths so if a student is absent, they get points based on the class they attend. For example, if you attend class1 and are absent you get 3 points, if you attend class2 or class3 and you are absent you get 2 points I need an iif statement that satisfies both conditions and then asigns points IIF([classattending="class1"and [inclass]="absent",3 otherwise, i...

Using Drop Down Menus Causes Outlook 2003 to freeze
Whenever I attempt to use a drop down menu in Outlook 2003, whether it's the "Insert File" window or the "Save As" window, Outlook freezes and I have to end the task using Task Manager. I can use other means to browse for files and folders within Outlook so I know I'm not have some strange issue with Explorer. I'm at a complete loss here. I've tried running "Dectect and Repair" and my next move will likely be to re-install Office 2003. Any help would be greatly appreciated. ...

Can'f find file GWXL97.xla
I have Excel 97 installed in Windows 95. I recently uninstalled many programs to clean up my drive. I suspect that the above file was deleted in the process. It is not in my Trash Bin because I cleaned out that bin. I lost my installation CD in the process of moving across the country. I don't know how to get that file, particulary since Microsoft no longer supports and programs in Office 97 If you have a suggestion, please respond Thanks This is the add-in for Novel Groupwise. It sounds like it was removed from your computer without first removing it from the list of installed add...

Going between documents using macros
I'm using Word 2004. I have two documents open. Is there a way, using macros, to go from Document 1 to Document 2, where I'll go to a certain line and copy it, then go back to Document 1 and save it using the text I have just copied. I have the macro down for going to the line, copying, and saving. What I need is the part where I go from doc to doc without regard to the document name. So I'll be in "Active Document" first, then I need to go to "?" (other Document) then back to "Previous Document." So I just need the navigation part. Here...

question about using strFilter with AND
Access 2007 database and using a form to filter records and import the results into a report. There are 4 list boxes: lstpayment lststore lstkeyword lstbuyer I can get what I want to work one at a time i.e. strFilter = "[store_name] = '" & Me!lststore & "'" DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter Works fine for the storename filter. But if I try to use an AND statement I get a type mismatch error strFilter = "[store_name] = '" & Me!lststore & "'" And "[payment_type] = &...

Using a NAME as a reference in another range
* The value in cell A1 varies depending on the number of rows of data brought down in a query * I have used the 'NAME' function to name cell A1 '_DataRows' I now want to use '_DataRows' to identify the end of a range in a SUMPRODUCT formula. (i.e. a formula something like this...) =SUMPRODUCT (--($D$1:$D$"_DataRows"=XYZ),--($D$1:$D"_DataRows"<>""),--($E$1:$E"_DataRows")) How do I do this (see examples below). Many Thanks, Craig ------------------------------------------- Example 1: --------------- If cell A1 = 2...

Finding last accessed point of time to a list of files
Hey guys This is a "funny" one In my folder "FILES" there are lots of files.I want to check which files beeing used within a period of time (speaking of some minutes). Some - not all - of these files are used by another application (some 3D-drawing application). Now I want to check which files beeing used by this application by checking the accessed point of time. I think the application maybe work this way in order to give me some trace to follow. As a final result I will separate (informly) the used files from the not-used files in the application-session...

Cannot send "group" email using outlook 2003
Outlook 2003 had been working okay, then went kablewy. WhenI try to send a "group" email "unexpected error occured" (Microsoft Word window) pops up and email will not send. This group has been working for 3 years. I do use a pop3 sever. I can send individual emails. Please help if possible. We have no idea what you mean by "group." There is no Outlook term to that effect. State clearly what you are doing, what happens when you try, and the steps you used to make Outlook got "kablewy." Outlook does not do that all by itself, you know. -- Ru...

Outlook 2003, using Word 2002 as editor
Outlook 2003 only seems to give Word 2003 as an option for the Word editor. We only have Word 2002 installed, so the option is greyed out. How do you get it to use Word 2002? Also, is there an option to make it come up with a 'yes/no' option when you get new mail? "You have recieved new mail, would you like to read it?" Like it does in Outlook XP? Thanks! Versions must match. Always been that way. -- Russ Valentine [MVP-Outlook] "Craig" <anonymous@discussions.microsoft.com> wrote in message news:29e301c3afa0$dfedd600$a601280a@phx.gbl... > Out...