Wildcards in Excel Formulas

I am working on an Excel spreadsheet and I am tyring to write a formula
to read the 4th character of previously entered data

For instance, codes entered in column A could be eadSvf1 and eadNvf1. I
need a formula to read that 4th character and put a 1 or 2 in column B
depending on if the 4th character is an S or an N

Hope that made sense and thanks in advance.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/2/2003 4:10:54 PM
excel 39879 articles. 2 followers. Follow

2 Replies
695 Views

Similar Articles

[PageSpeed] 43

got it, thanks anyway, I ended up using the MID function



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
10/2/2003 5:19:51 PM
Hi Max,
=IF(MID(A1,4,1)="S",1,IF(MID(A1,4,1)="N",2,"Neither")) and fill down.

If the text can also be lower case use...
=IF(upper(MID(A1,4,1))="S",1,IF(upper(MID(A1,4,1))="N",2,"Neither")) and
fill down.


-- 

John

johnf202 at hotmail dot com


"Max997" <Max997.uojiy@excelforum-nospam.com> wrote in message
news:Max997.uojiy@excelforum-nospam.com...
> I am working on an Excel spreadsheet and I am tyring to write a formula
> to read the 4th character of previously entered data
>
> For instance, codes entered in column A could be eadSvf1 and eadNvf1. I
> need a formula to read that 4th character and put a 1 or 2 in column B
> depending on if the 4th character is an S or an N
>
> Hope that made sense and thanks in advance.
>
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~ View and post usenet messages directly from http://www.ExcelForum.com/
>


0
me5306 (285)
10/2/2003 7:40:23 PM
Reply:

Similar Artilces:

Wildcard in query
Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards It will not work like you have it as you are saying Like "Honda Prelude...

Automatic sorting in excel
I would like to be able to have a column sorted alphabetically contiuously. In other words, whenever data is entered, the whole column gets arranged alphabetically without having to "click' on the sort function. I am not a power user of excel, but I now understand the basics of running a macro. I have been guided to use the following macro example and adapt it to my worksheet. I have tried a few changes, but I cannot get it to work. Lets say the filename is "mybook" and the worksheet name is "sheet1" and the range I want to sort automatically is B1:B20 ...

sumproduct with wildcards?
Hi, will sumproduct() allow the use of wildcards? I'm trying to sum a portion of a large spreadsheet. One column is numerical, the other is text. All of the entries in the text column begin with common values but differ at the end, hence my attempt at the use of a wildcard. But it doesn't seem to work. Any suggestions? thank you. jim -- "If you carve yourself to suit everybody, you'll soon whittle yourself away" No, SUMPRODUCT will not work (directly) with wildcards. You can use something like this: Sum values in column B where the values in column begin wi...

Print Excel files from a BAT file.
I would like to print to a network printer a bunch of Excel files using a BAT file. The network printer drivers are installed in Windows XP. I read the Excel parameters but none are for sending a print job to a printer. Can someone provide a BAT Start commnad line for me? -- Victor Manske Computer Network & Systems Consultant ...

Excel to Quark
Does anyone know a resource for importing tables into Quark 6 from Excel X? Any help would be greatly appreciated Do you actually want the table or just an image of the table. If an image will suffice then hold down SHIFT and use 'Copy Picture', and then in Quark just paste it as you would anything else. You could also look for an Import option in Quark if there is one. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It'...

Numbering items in Excel
I hv put few items in Excel rows. I want to put serial numbers on items like 1,2,3, 4 on each row. Is there a command which numbers the items serial wise. When I print them , item number should appear number wise. Plz advise tiwana@eastlink.ca Hi Paul, You can use the fill handle to fill down a constant (like 1) http://www.mvps.org/dmcritchie/excel/fillhand.htm But if you want the numbers to change when you sort you might use something relative to the current row number. Select A2 and drag the fill handle down. Double-click the fill handle will be faster if you have something to the...

excel 2000 vs excel 2003
Hello, I'm very new to Excel, never even seen the program open. I registered for an online class at my community college for Introduction to Excel. The course requirements say you can be using excel 5.0, 95, 97, or 2000. When I went to buy the program, office 2003 professional was the only one available. I've searched on the internet and found lots of articles that say there's not much change from 2002 to 2003. But I can't find info about the differences from 2000 to 2003. My question is: Is the look and general feel, use and layout of the program very differe...

I want to learn Macros in excel, please let me know about it
See http://www.mvps.org/dmcritchie/excel/getstarted.htm ...

how to create a bookmark automatically in Excel?
Hi all, I have a set of experiments that I have to run. Each time I not only record down all the experiment data but also the best result so far. I would like to create a hyperlink which could be automatically linked to the best result that is in record. The result records could be huge so it's important to have this bookmark which can point me to the best result fast, via one single- click... I've tried manually creating and inserting hyperlinks, and it worked... And now the question is how to do this automatically? Thanks! A little more information is needed. How is your data...

Number of columns in Excel
Can I add more columns, ie greater than 256 No, that's the maximum number of columns available in Excel. Mike wrote: > Can I add more columns, ie greater than 256 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

Autocorrect with wildcards
I work in a company in the UK which works with Norwegian clients a lot, often people with � in their name. I want to set up people's computers so when the type aaa in a word, it comes out with �. Eg. if they type 'Paaal' they get the name 'P�l'. When I program autocorrect to change aaa to � it only does it when aaa is typed as a word, not within a word. Is there a character I can use as a wildcard, or some other way to get autocorrect to do this? -- bluebottle There is no way to make AutoCorrect do what you propose. However, there is a default k...

Alias with wildcard character
Can I use wildcard characters (such as * for 1 or more characters) in a email address alias? For example, I would like to use the alias case_*.mydomain.com to forward to an existing email account. I'm trying to setup an issue tracking system, which wants to embed the case number into the email address. For example, all email related to case #588 will be sent to address case_588@mydomain.com and that email will be forwarded to an Exchange account. Case number can be from 1 to 6 characters, so manually creating aliases is not an option. Any help/suggestion you can provide is much ...

excel sorting
I am trying to sort data from our database that has client names in al uppercase, and all company names with the first character uppercase an the rest lowercase. ie. JOHN STEVENS Apple Tree Studio I'd like to sort the sheet with the companies first alphabetically the the client names alphabetically. How can I do this -- Message posted from http://www.ExcelForum.com One way is to use a helper column. Assuming the names are in column A starting in A2, insert a new column B and enter in B2 =-EXACT(A2,UPPER(A2)) and fill down. The sort on columns B and A. HTH Anders Silven "c...

Need to convert Supercalc 5 into Excel
Does anyone know how to do this? Thanks -- rmccoy0025 ------------------------------------------------------------------------ rmccoy0025's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30360 View this thread: http://www.excelforum.com/showthread.php?threadid=500149 You will need a copy of Supercal to save the file in XL format Tried e-bay? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rmccoy0025" <rmccoy0025.21h60z_1136990703.8583@excelforum-nospam.com> wrote in message news:rmccoy0025.21h60z_1136990703.8583@ex...

Analyzing 2 excel workbooks
Is it possible to analyze 2 excel workbooks that are almost the same, to find the differences between them? I don't know the size of your workbooks, but one solution for comparing two worksheets is on a third worksheet, write this formula in cell a1 and copy over the area you need to compare: =if(Sheet1!a1=Sheet2!a1,"","Different") This says if cell a1 is the same on both sheet 1 and 2 do nothing (""), if the cells are not the same it will type the word "Different". If you have multiple sheets, the areas are extremely large, or the...

PDF to Excel
Hi, I have an Excel file saved as PDF format. How can I retreive it back into Excel? Hi Hhalil, That's not a good situation to put yourself in they are completely different and not reversible, you will have to copy and paste. PDF and Excel http://www.mvps.org/dmcritchie/excel/pdf.htm If you saved the file as PDF you should still have the orginal Excel file wherever it was when you last saved as Excel. HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "...

Excel 2002 Check for duplicates
Sorted an Excel list for a Pivot Table by Check number for the years 1997-2005. Any easy way to check for duplicates w/o getting into VBA? Check# 7894 7895 7895 7896 Try this, Dave: http://www.officearticles.com/excel/delete_duplicates_in_microsoft_excel.htm ************ Anne Troy www.OfficeArticles.com "~Dave~" <dlcopesr@yahoo.com> wrote in message news:11gjl0lhin4v28c@corp.supernews.com... > Sorted an Excel list for a Pivot Table by Check number for the years > 1997-2005. > Any easy way to check for duplicates w/o getting into VBA? > > Check# > &g...

COLUMNS IN EXCEL #2
Hello! Wonder if you can help? Is it possible to lock one specific column on an Excel spreadsheet without protecting the entire sheet? Thanks for your help Sarah x Try this, Sarah: http://www.officearticles.com/excel/cell_locking_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Sarah_Lecturer" <SarahLecturer@discussions.microsoft.com> wrote in message news:98A80F13-1A62-4F64-BC71-8FB9731CE3FB@microsoft.com... > Hello! > > Wonder if you can help? Is it possible to lock one specific column on an > Excel s...

How can i made a monte Carlo simulation with Excel ?
Do I need some additional stuff ? Thanks Bberni - You could use only Excel features, like the worksheet function RAND() to generate random numbers, the Data Table command to repeatedly recalculate and collect values, the worksheet function FREQUENCY() to summarize results, and the Chart Wizard. Or, you could use an add-in, like my RiskSim at www.treeplan.com, @RISK at www.palisade.com, or Crystal Ball at www.decisioneering.com. - Mike www.mikemiddleton.com +++++++++++++++++++++++++++ "Bberni" <Bberni@discussions.microsoft.com> wrote in message news:515AE599-7F1C-...

Visio and Excel #2
Currently I create an organizational chart by importing an Excel file into a software program called OrgPlus. Does Visio allow importation, primarily from an Excel file or even a Notepad file? Thanks, Daniel In article <5253ECA1-AECF-429B-ABF8-EC6E02727868@microsoft.com>, =?Utf -8?B?RGFuaWVs?= wrote: > Does Visio allow importation, primarily > from an Excel file or even a Notepad file? > Yes to both. You don't say which version you're using, but if you're using 2003 select File, New, Organisation Chart and the select Organisation Chart Wizard. It will wal...

Excel Macros
Hi: I have a workbook with 14 worksheets. I need 14 macros I want the name of the macro to be the name of each worksheet. ex. FA2E, FA3E, FA4E, etc. the macro is the same except for referencing the worksheet name. I have the first macro and am copying/pasting 14 times, changing the worksheet name and macro name. However, it doesn't work properly. About the 5 copy/past excel seems to want to call the macro by the name of the workbook, dot, worksheet. What am I doing wrong. When I open up tools, macro, I want to see the 14 names as stated above. Because, I have a final ...

Can I break an external link in Excel 97?
Try Bill Manville's excellent "FindLink" utility .. It's available at Stephen Bullen's page: http://www.oaltd.co.uk/MVP/Default.htm -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- stadco <stadco@discussions.microsoft.com> wrote in message news:BE79E018-3460-47F7-A8D3-369889E7AF9E@microsoft.com... > Re: Can I break an external link in Excel 97? ...

MTD running total formula
I am building a spreadsheet that tracks sales on a daily basis. What I want to do is have a column that shows where we should be in sales on a particular day. Normally that would be simple but my sheet has everyday of the month in it and I want to exclude Sat, Sun and Holidays from the formula. The sheet is set up as follows with the sales target for the month in cell D1 and the amount of shipping days in D2. A B C D Sales Daily Target 8 Thu 1/1/04 9 Fri 1/2/04 10 Sat 1/3/04 11 Sun 1/4/04 Thanks in advance, BW --- Message posted from http://...

Excel #21
I have a spreadsheet comprised of entries like this : 726016004084 ALV BAG DANDELION United Natural Foods Inc. 726016004084 ALV BAG DANDELION Select Nutrition Dist. 726016004084 ALV BAG DANDELION Threshold 726016004190 ALV BAG ECH-GOLD-SEL United Natural Foods Inc. 726016004213 ALV BAG ELDERBERRY Threshold 726016004213 ALV BAG ELDERBERRY Select Nutrition Dist. What I want to do is reduce the list so 726016004084 ALV BAG DANDELION United Natural Foods Inc., Select Nutrition Dist., Threshold 726016004190 ALV BAG ECH-...

Is there an excel "bid form" template?
I'm looking for an Excel "bid form" template. Does that exist? ...