extract info from cell, then count

I have a 2-part question: 

(i) I have 1000's of e-mail addresses but want to extract the countr
from the e-mail i.e. abc@def.de, where de (Germany) is needed.  How d
I isolate the ".de" (and others eg .fr, .edu, .com etc etc)

(ii) Having done the above, I then need to do a count.  Rather than us
COUNTIF and include the code for every country in the world, is ther
any other way of counting?  I guess a Pivot table?

thanks, cathal.....

--
Message posted from http://www.ExcelForum.com

0
1/5/2004 11:37:41 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
265 Views

Similar Articles

[PageSpeed] 36

One way:

    
=MID(A1,FIND("^",SUBSTITUTE(A1,".","^",LEN(A1)-LEN(SUBSTITUTE(A1,".",
""))))+1,255)

A pivot table would be an easy way to summarize.

In article <cathal.zk40r@excelforum-nospam.com>,
 cathal <<cathal.zk40r@excelforum-nospam.com>> wrote:

> I have a 2-part question: 
> 
> (i) I have 1000's of e-mail addresses but want to extract the country
> from the e-mail i.e. abc@def.de, where de (Germany) is needed.  How do
> I isolate the ".de" (and others eg .fr, .edu, .com etc etc)
> 
> (ii) Having done the above, I then need to do a count.  Rather than use
> COUNTIF and include the code for every country in the world, is there
> any other way of counting?  I guess a Pivot table?
> 
> thanks, cathal......
0
jemcgimpsey (6723)
1/5/2004 11:54:14 AM
Reply:

Similar Artilces:

How do I get absolute values for a range of cells?
I various spreadsheets with a mixture of positive and negative numbers. I would like to make them absolute values. I believe a macro would be necessary. Hi Terry i've just tried this on a SMALL data set - so you might like to test it a bit more thoroughly before believing in it ... but i selected the range i wanted to convert to positive values and did edit / replace find what: - replace with: <<leave blank>> clicked Replace All seemed to work. Cheers julieD "Terry" <Terry@discussions.microsoft.com> wrote in message news:6F41A1F8-1E8C-4643-92B4-8CD...

Remote Email Extraction
Can anyone tell me if it is possible to view the emails from my outlook but using another computer from another site? I ask this because if my computer fails then clearly I can not logon to Outlook on my computer till it is repaired but I was wondering if I could see my emails using another computer. If there is a way then please keep your answers nice and simple please. Thanks. -- Kesh Patel Dear Kesh, are you using an Exchange Server or alocal installation of Outlook with a "PST" file? If you use Outlook as "single" installation you can leave all your emails on ...

How do I merge cells in Excel, like just 2 cells to make one big .
How do I merge 2 cells on a page of excel? NOT 2 workbooks, NOT a whole line, but just 2 cells to make one bigger one? Select them, do format>cells>alignment and check merge Let me also say that most (all) experienced excel users try to avoid merging if possible, you always seem to run into problems later on by using it. Most layouts can be done by centering across cells and removing of gridlines without using these beasts Regards, Peo Sjoblom "chattacat" wrote: > How do I merge 2 cells on a page of excel? NOT 2 workbooks, NOT a whole > line, but just 2 cells t...

Automatic Updating of cells with formulas...
Hi, I have a spread sheet with several rows that use formulas t generate their contents, but the problem is that everytime I alter th data in my table, it has to recalculate all the derived cells and i takes like 10 seconds every time I change something in my data. Is i possible to disable automatic updating of cells, so that I can ente all of my new data and then enable it again so it can updat everything? Thanks for your help. R -- Message posted from http://www.ExcelForum.com Hi tools / options/ calculations tab - set to manual Cheers JulieD "Edge118 >" <<Edge118...

Cells not recognised for charts
I have a table of public domain data from Nomis (The UK Gov't office that provides data on employment). I have created the table manually and it works fine. The Nomis one will not display as a chart. I have copied the formatting of the manual one to the Nomis version, so it's not that. All cells are 'general' in both tables. If I double click in each cell, it does something to it and it will then be recognised but I cannot see anything which tells me what the difference is before and after. If anyone can help me understand what the issue is, and even better, how to solve i...

Cell copying itself when I don't want it to
Hi, I am using excel 2003. When I put in a formula, for example, in A1, if there is a formula in A2 it changes the formula from A2 to match the formula in A1. What can I do to make it stop this? Thanks. See "Extend formats and formulas to additional rows" in the help menu. "CWTaylor" wrote: > Hi, > I am using excel 2003. When I put in a formula, for example, in A1, if > there is a formula in A2 it changes the formula from A2 to match the formula > in A1. What can I do to make it stop this? Thanks. > ...

Counting a date range
Hi. I'm used to operating in Access, but need to do some analysis in Excel because my users don't have Access. I have a worksheet called "Master" with a column called "Date of Referral". I've created a new worksheet called "Analysis" and want to count all the records for the month of October. Any ideas? Assuming REAL dates in column A. Do not use a:a =sumproduct((month(a2:a22)=10)*1) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Julie" <Julie@discussions.microsoft.com> wrote in message news...

default indent in Powerpoint table cells
Hi, In powerpoint 2007, I would like to set a default so when I make a new table, it has an indent, so bullets (int the table cell) are hanging. Basically, when you look at the ruler for each table cell, the top arrow and bottom arrow are lined up, but I don't want that. Hi, Sakols, I believe to make changes to the default fonts, margins and bullets used for titles, other text boxes or tables that you want for each new presentation, you need to open a new presentation using a template that is closest to your needs. Leave the presentation blank, then make changes in th...

thanks 4 info
thanks 4 info ...

PIVOT
Hi experts, I want to enable users with few knowledge of pivot techniques to change the grouping of a pivot chart resp. the underlying pivot table. The idea is, to have a changeable cell value beneath the chart to enter the group (groups of 2, 4, 6, 5, etc...). The associated pivot table shall change it's groupings accordingly, thus forces to change the associated chart. Any idea, how to achieve this? Thanks and have a nice day Michael Hi, What kind of groupings are we talking about when we say 2, 4, 6, 5? What is being grouped and how? What fields, row fields, more than one row fie...

How can I Unlock cell/worksheet?
I received a spreadsheet that has been used as a form and only the cells where information is to be populated are accessible. I thought I could do a Save As or even copy all the contents and paste them into a new spreadsheet so I can alter the cells that are not accessible, but I am unable to do this. I cannot contact the originator for an editable version. How can I work around this? XL passwords are not very secure... Check out this link... http://www.mcgimpsey.com/excel/removepwords.html Please use these powers for good instead of evil... -- HTH... Jim Thomlinson ...

Cell protection with hidden columns
Is there a way to view hidden columns when the cells in those columns are protected? Thanks, Anat You could unprotect the worksheet. Visit JE McGimpsey's site: http://mcgimpsey.com/excel/removepwords.html Or you could toggle a setting. Tools|Options|transition tab|check transition navigation keys. Then find an unlocked cell and type = (just an equal sign) Then use the mouse (or arrow keys) to drag through the hidden column range. That unlocked cell could be on any worksheet if you don't have unlocked cells available. Anat wrote: > > Is there a way to view hidden columns...

Cell Phone Information Site
Information and reviews on cellular phones,cellular phone plans,cellular phone accessories,cellular phone ringtones and much more. www.allcellphone-site.com --- MAF Anti-Spam ID: 20060707000315U0o0MrE8 ...

How to put the selected cell onscreen, but not upper left
I want to put the currently selected cell onscreen but not at the top left. I want a cell in Col E selected, but I want the user to see cols A-E. I can manipulate XL to select A9, then scroll there, then select E9, but it seems there must be a better, more direct way. Right now I have: Application.Goto Reference:=ActiveCell, Scroll:=True which puts the selected cell (E9) in the upper left of the display. Thanks for the help. Try this: Sub marine() Range("E9").Select Application.Goto Reference:=ActiveCell, Scroll:=True ActiveWindow.ScrollColumn = 1 End Sub -- Gary''s St...

How do I count odd numbers in a range?
Could someone please help me!!! I am trying to count how many odd and even numbers there in a range. I have tried to use the 'countif' , 'iseven' and 'isodd' formulas. However, I have been unsuccessful. Can someone please let me know if this can be done? Here's a formula from an older post by Peo Sjoblom that seems to do what you want: =SUMPRODUCT(--(MOD(A1:A22,2)=1)) Adjust the range as necessary. tj "Rob" wrote: > Could someone please help me!!! > > I am trying to count how many odd and even numbers there in a range. I have > trie...

Setting colors/borders for cell
What is the VB code syntax for setting background colors and borde style for a particular cell. Worksheets("Sheet1").Cells(1,1)???????? = ??????? .Borderstyle doesn't work in this syntax, does it? thank -- k48 ----------------------------------------------------------------------- k483's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1079 View this thread: http://www.excelforum.com/showthread.php?threadid=27427 Hi simple solution: Record a macro while doing it manually :-) e.g. background: activecell.interior.colorindex=3 -- Regards Frank...

A fromula to pick up nontext in cell
I 2 columns, A & B. I have data in colunm a cells that are text but the text varies from cell to cell, over in some of the cells there is the words INACTIVE. I want a fomrula that will look to colunm A and return the inactive text into column B else if the colunm is not INACTIVE return blank cells. hint I tried VLOOKUP, IF THEN, ISTEXT and no luck. Please HELP!!! chris =IF(A1="INACTIVE",A1,"") or is that too simplistic? -- HTH Bob Phillips (remove nothere from email address if mailing direct) "chris" <chris@discussions.microsoft.com> wrote i...

Counting a Range of Values
Who likes a challange? I have a workbook that has multiple worksheets. A number of th worksheets are identical and are used by multiple users to input thei own data. I need to extract a set number of rows from 2 of the column on each of these given sheets. To make matters more interesting, I onl want to extract the row if there is information in it. If the field i blank, leave it! I want these to be extracted into another worksheet i the same workbook. After I perform a calculation on each of these rows that were exported I will get a new column which will contain a time. (ie: 8:45) I ...

Change the formatting of row by select a single cell & Editing should be working #2
Hi All; I require such a sheet code which can help me in Changing the formatting of row (like A1:A25) of selected cell (A4 is Selected) and allow me to use editing (Cut, Copy, Paste, Undo etc). Currently I'm Using following code but this does not allow me to use editing (Cut, Copy, Paste, Undo etc) and I can not decide the range for working this sheet code. Any kind help is appreciated. Private Sub Search_Click() Sheets("Quick Search").Visible = True ActiveWindow.SelectedSheets.Visible = False Sheets("Quick Search").Select End Sub Private Sub Workshe...

Loacate a range of cells
Excel is Company Forms, that have book numbers in seveval range of cells. VB6 we enter the book number which has a var. and a quanity var. So I want to find the book Number in range B30:B65 and put the quanity in range A30:A65. This will be on Sheet1. Any help would be appreciated. Thanks Don Maybe =vlookup() against a table that contains the book number in column A and quantity in column B (probably on a different sheet???) Donald Johnson wrote: > > Excel is Company Forms, that have book numbers in seveval > range of cells. VB6 we enter the book number which has a > var....

AA INFO NOT POSTED CORRECTLY
---This is a very important issue---- Sometimes when GP post a transaction in GL with AA. Only the GL part of the transacction is transfered to the posted records (GL20000). The AA records on tables AAg10000, AAg10001, AAg10002, AAg10003 are not transfered and then the posted information for the original GL transaction is incomplete or corrupeted since there is no AA information related to the posted GL transaction. I would like MS Dynamics to update this reported issue. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the mos...

counting cells #4
What fromula do I use if I want a cell to count how many times a number (time) occurs in a range of cells. e.g. cells a1 a2 a3 a4 a5 0700-1600 0700-1200 1200-1600 0800-1100 1100-1500 If I wanted the formula to look for how many times 0700 and 0800 occured, the answer would be 3. Thanks in advance Jason This counts the 0700: =SUMPRODUCT(--(LEFT(A1:A5,4)="0700")) This counts 00700 and 0800 =SUMPRODUCT(--(LEFT(A1:A5,4)={"0700","0800"})) best wishes -- ...

Copying Colored Cells from Excel to Powerpoint
Office 2007: I have several Excel Spreadsheets that have sporatic colored cells. It is not done by conditional formatting. Is there a way to copy these colored cells from Excel to PowerPoint without losing the color shading? Thanks for any help!!! Try setting up a color printer and specifiying it as your default printer. You may need to choose the Paste Options button after you paste -- or use Paste Special on the Home tab to paste as Excel object. If it gets converted to a PPT table, I think you'll lose the conditional formatting. -- Echo [MS PPT MVP] http://www.ec...

Can contents of a cell be used in header/footer?
All, I am interested in making a custom header that contains text from a certain cell. There are standard fields that can be added to a header or footer using the standard buttons in the header or footer dialog box. These are: &[Page], &[Pages], &[Date], &[Time], &[Path]&[File], &[File], &[Tab] I was wondering if I could create a custom field for my header that would insert the contents of a certain cell. For instance, cell A7 contains a Vendor name, let's say "XYZ Supply". I would like to create a header that says, "Vendor: XYZ Supply&q...

merged cells and wrapping text
I have merged a few adjacent cells in a row, then asked Excel to wrap the text with the merged cell. It refuses. I was originally trying to do this in code, but I now find that it can't be done even when I try and do it manually. (Using Office XP) Anyone know a work-around? cheers Rod Try the following code: Sub alpha() With Range("a1:b4") .HorizontalAlignment = xlGeneral .VerticalAlignment = xlTop .WrapText = True .MergeCells = True End With End Sub Regards, Alasdair Stirling "Rod" wrote: > I have merged a few ad...