Parsing Names in Excel

I have a list in Excel from A1:A100 with names like the ones mentioned 
below:

      Michelle Fooo (michelfoo@exchange.SASMOM.com)
      Myhaass Smith (mysmith@exchange.SASMOM.com)


I want to parse them using string functions to the following format

michelfoo@SASMOM.com
mysmith@SASMOM.com

Any ideas how to do this using a formula?

Thanks in advance
Michael


0
12/23/2006 10:44:32 PM
excel 39879 articles. 2 followers. Follow

3 Replies
652 Views

Similar Articles

[PageSpeed] 58

Michael

Are you wanting to strip all the name out too or just remove the exchange.?

If the latter just use edit>replace What:=exchange. and leave the with blank

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk


"Michael" <michalakis_michael@hotmail.com> wrote in message 
news:OZNDrPuJHHA.3952@TK2MSFTNGP02.phx.gbl...
>I have a list in Excel from A1:A100 with names like the ones mentioned 
>below:
>
>      Michelle Fooo (michelfoo@exchange.SASMOM.com)
>      Myhaass Smith (mysmith@exchange.SASMOM.com)
>
>
> I want to parse them using string functions to the following format
>
> michelfoo@SASMOM.com
> mysmith@SASMOM.com
>
> Any ideas how to do this using a formula?
>
> Thanks in advance
> Michael
>
> 

0
12/23/2006 10:56:11 PM
The solution was

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

Jason Lepack provided the answer.

Thanks
Michael

"Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message 
news:eoO$NWuJHHA.3424@TK2MSFTNGP02.phx.gbl...
> Michael
>
> Are you wanting to strip all the name out too or just remove the 
> exchange.?
>
> If the latter just use edit>replace What:=exchange. and leave the with 
> blank
>
> -- 
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
> www.nickhodge.co.uk
>
>
> "Michael" <michalakis_michael@hotmail.com> wrote in message 
> news:OZNDrPuJHHA.3952@TK2MSFTNGP02.phx.gbl...
>>I have a list in Excel from A1:A100 with names like the ones mentioned 
>>below:
>>
>>      Michelle Fooo (michelfoo@exchange.SASMOM.com)
>>      Myhaass Smith (mysmith@exchange.SASMOM.com)
>>
>>
>> I want to parse them using string functions to the following format
>>
>> michelfoo@SASMOM.com
>> mysmith@SASMOM.com
>>
>> Any ideas how to do this using a formula?
>>
>> Thanks in advance
>> Michael
>>
>>
> 


0
12/23/2006 11:26:40 PM
This does not look like it removed the word "exchange." from the email 
addresses as requested.  This would be better suited if "exchange." always 
shows up the way it does in your example.

=MID(A1,FIND("(",A1)+1,FIND("@",A1)+1-FIND("(",A1)-1)&MID(A1,FIND("@",A1)+10,FIND(")",A1)-FIND("@",A1)-10)

-KC

"Michael" <michalakis_michael@hotmail.com> wrote in message 
news:%23cQdPnuJHHA.320@TK2MSFTNGP06.phx.gbl...
> The solution was
>
> =MID(A1,FIND("(",A1)+1,FIND("@",A1)-FIND("(",A1)-1)&MID(A1,FIND("@",A1)+10,FIND(")",A1)-FIND("@",A1)-10)
>
> Jason Lepack provided the answer.
>
> Thanks
> Michael
>
> "Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message 
> news:eoO$NWuJHHA.3424@TK2MSFTNGP02.phx.gbl...
>> Michael
>>
>> Are you wanting to strip all the name out too or just remove the 
>> exchange.?
>>
>> If the latter just use edit>replace What:=exchange. and leave the with 
>> blank
>>
>> -- 
>> HTH
>> Nick Hodge
>> Microsoft MVP - Excel
>> Southampton, England
>> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
>> www.nickhodge.co.uk
>>
>>
>> "Michael" <michalakis_michael@hotmail.com> wrote in message 
>> news:OZNDrPuJHHA.3952@TK2MSFTNGP02.phx.gbl...
>>>I have a list in Excel from A1:A100 with names like the ones mentioned 
>>>below:
>>>
>>>      Michelle Fooo (michelfoo@exchange.SASMOM.com)
>>>      Myhaass Smith (mysmith@exchange.SASMOM.com)
>>>
>>>
>>> I want to parse them using string functions to the following format
>>>
>>> michelfoo@SASMOM.com
>>> mysmith@SASMOM.com
>>>
>>> Any ideas how to do this using a formula?
>>>
>>> Thanks in advance
>>> Michael
>>>
>>>
>>
>
> 


0
12/27/2006 4:16:26 PM
Reply:

Similar Artilces:

Excel Drop Down Boxes #2
Question; does anybody know how to add additional data to a previous drop down box? I have created a form that has several drop down boxes a while ago, and due to some information that has been change I need to input more info in the drop down boxes but for some odd reason I cant remember how I executed these actions!! If it helps I am using Excel 2000. -- CRS ------------------------------------------------------------------------ CRS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27444 View this thread: http://www.excelforum.com/showthread.php?threadid=46...

PDF Table to Excel Spreadsheet
This has been asked in numerous places all over the web but I have not found a workable solution. We have a mainframe system that generates PDF reports for billing. However, few people in our organization these days speak mainframe. We need Excel to do calculations on the data. I began working on a method for converting using UEdit Macros and an Excel macro because the fields are fixed-width. My attempts have been unsuccessful. Here is what I want to accomplish: -Copy a region of text from a PDF -Paste into Excel -Break the values out into the appropriate cells. http://www.google.com/sea...

Using variable names for cells
I seem to remember a technique where I could assign a variable name to the contents of a cell so that whenever I wanted to use the contents, all I had to do was call up the variable name. Unfortunately I cannot find the way to set up the process. Any suggestsions or ideas would be appreciated. Thanks and a Happy New Year. -- Take out the trash to reply '05 FLHTCUI Hi dim rng as range set rng=activesheet.range("A1") msgbox rng.value -- Regards Frank Kabel Frankfurt, Germany Ultraglide wrote: > I seem to remember a technique where I could assign a variable name to >...

How do I use excel names with INDIRECT with charts
Hello, I want to create a Chart that does not directly reference cell-ranges (i.e. A1:A6), but excel-names that make the reference sheet-independent. My aim is to be able to copy one chart to other worksheets, which have their dataareas at the same places like the source-sheet. Problem: This works fine in cells but not in charts Excel name definition: =INDIRECT("R3C2";0):INDIRECT("R3C5";0) Any idea about this? Thanks in advance, Holger. You have to include the sheet name in the final formula that you want XL to use. -- Regards, Tushar Mehta www.tushar-mehta.co...

Look up names/addresses for specific category
I want to mail to clients in a specific category - how do I pull up names/address for a specific category? sandy <sandy@discussions.microsoft.com> wrote: > I want to mail to clients in a specific category - how do I pull up > names/address for a specific category? In your Contacts, switch to the By Category view, select all the addresses from the desired category, right-click the selection and choose "New Message to Contact". -- Brian Tillman ...

Excel
i use Excel with office xp. i have a form we have made that requires client Name, Address, Phone, Fax etc etc etc... currently i keep all those contacts within my outlook contacts... is there a way i can type in the client business name in that cell, and have excel do one of the following: autofill with available address, phone, fax, PC, City/Town, contact... or... give me a choice of available contact... keep in mind, the Address, Phone, Fax, PC, City, Contact Name are all different rows and colums... Any thoughts Big One method....... Export your Contacts from Outlook as a *.CSV f...

Excel VB Code Error
I am trying to create a .csv file from an Excel workbook using a VB module and am wondering if someone can tell me what's wrong with the below code: Public Sub CSV2() Application.DisplayAlerts = False Sheets.Add ActiveSheet.Name = "Temp_10001" For Each ws In Worksheets If ws.Name <> "Temp_10001" Then ws.UsedRange.Copy Sheets("Temp_10001").Range("A1").Activate x = ActiveSheet.UsedRange.Rows.Count If x > 1 Then ActiveCell.Offset(x, 0).Select ActiveCell.PasteSpec...

Problems saving excel file from outlook
I have an EU that whenever he saves an excel file from outlook, it will rename the file (by adding the number "2" at the end of the file "file2.xls") and it also changes all the embedding links in the document. (The embedding link of F:\users\file.xls changes to c:\documents and settings\file.xls) Are you setting a Hyperlink base for your links? See Menu > File > Property > Summary tab. Put the original folder location there and when you move or save the file the links will not be renamed. Also assigning a letter to the drive can cause problems (not ...

Excel tab as windows
Is there a way while running Excel to be working on one tab and open another tab as a smaller window that is always on top. So I can enter information in both tabs without having to minimize, maximize, or switch back & forth? Thanks C. Fillmore Hi you could align the windows so you can see both at the same time. But a 'always on top' option does not exist AFAIK -- Regards Frank Kabel Frankfurt, Germany surveyorinva wrote: > Is there a way while running Excel to be working on one tab and open > another tab as a smaller window that is always on top. So I can > enter in...

can cvs files be opened using excel viewer
i have a user who cannot open cvs files with excel viewer but can ope with the full version of excel...anyone experienced this problem and i so, can you pls post the resolution -- darrie ----------------------------------------------------------------------- darriel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2388 View this thread: http://www.excelforum.com/showthread.php?threadid=37522 There is nothing in the description of the Excel Viewer to suggest that it can read anything other than true XLS files. http://support.microsoft.com/default.aspx?scid=kb;...

scramble names on work roster
Hi all.. I got this roster on a excell work book. Could you please help me to get the following. the names on the first collumn has to be put random in the grid below the roster. but first check and see if the person work morn shift. only if person 4 is working morning on monday 18 may then his name can be added to the grid below every week i print one of these rosters. these names must be scrambled every week on the below grid. the last condition is that the names must be duplicated as littel as possible From time to time i add or take away names. Geen uitruilings sal ged...

how do i search an excel sheet for links to another spreadhseet?
Is there a Find function that allows me to look for links in a sheet? When I can't find links, I'll use Bill Manville's FindLink program: http://www.oaltd.co.uk/MVP/Default.htm Flutie99 wrote: > > Is there a Find function that allows me to look for links in a sheet? -- Dave Peterson ...

Excel Reader scroll bar???
I'm using an Excel viewer to read e-mail attachments from my home office. I don't have Excel installed on my machine, only the viewer. Often I do not have the horizontal scroll bar. I have tried maximizing the window and still no luck. Any solutions for this? Thanks ...

data extraction from excel
I need to pull information from one collumn. If the collumn is comprised of yes's and no's, can excel create a list of just the yes's? Try something like this: Assuming your list is in A1:B100, with A1: Question B1: Response C1: Response C2: Yes D1: Question (the same col heading as A1) E1: Response (the same col heading as B1) Select your list (A1:B100) Data>Filter>Advanced Filter List Range: (already selected) Criteria Range: C1:C2 Click the [OK] button to filter the list in place. OR To copy the matching records to another area: Click: Copy to another location Copy...

batch importing of mailing lists to Excel
I've got a mailing list of around 1000 names, stored in both Word and Entourage like this: First Name Last Name <e-mail address> Once I’ve eliminated the < >, do I then have to individually copy & paste (or type) all the info in three Excel columns? Or is there a simpler solution for batch importing of mailing lists to the Excel spreadsheet? "bgsignal" wrote: > I've got a mailing list of around 1000 names, stored in both Word and > Entourage like this: First Name Last Name <e-mail address> > Once I’ve eliminated the < >, do I then ...

Excel Slooooow. It takes 4 to 6 seconds to perform each input or command
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have just installed Excel 2008 on a new installation of Snow Leopard and it does not work properly. It takes ages to register every input or command, like a large Lag. <br><br>EG. each figure or text, input into cells takes 4 seconds from hitting enter to accepting the input. Changing the width of a column takes 6 seconds from letting go of the mouse after dragging to the new width. etc etc. <br><br>I have checked for updates. Any ideas? > I have just installed Excel 2008 on a n...

pasting excel data in a powerpoint slide
I am attempting to paste an excel data table in a powerpoint slide but once pasted, the data is cut off. If I double click and enter the excel data table and resize the window from Powerpoint, the same data shown simply stretches to the new size. I have tried numerous ways of pasting from the excel sheet and it still acts the same. I tried a new workbook and was able to resize correctly. But to recreate all of the data and formulas in a different workbook would be difficult. Is there a setting or something in excel that is not allowing me to resize this particular workbook? Thanks...

Simple coding in Excel
I have created a series of forms which go from one to another via a macro, which is simply: Sheets("Implementation").Select DialogSheets("Implementation").Show (created by record macro). There are two issues: 1. This same code does not work when assigned to auto_macro for the first form? 2. After moving to subsequent forms, the previous forms are visible (but not accessible) in the background. This just looks untidy and I would like a command to close them. Many thanks Have you tried? Sheets("Sheet Name").Visible = True Sheets("Sheet N...

excel time formulas
I have a column which shows a format of hours, mins and seconds an would like to find a formula which can convert it into minutes only i.e 10:50.2 -- Message posted from http://www.ExcelForum.com Format the cells as [mm] -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "aly1cat >" <<aly1cat.185f1c@excelforum-nospam.com> wrote in message news:aly1cat.185f1c@excelforum-nospam.com... > I have a column which shows a format of hours, mins and seconds and > would like to find a f...

excel 2007 home key
how do i get the home key to work. excel 2003 did it in tools/transition When you say get the home key to "work", do you mean have it return the active cell to A1? Click on the Office Button (top-left of screen) Click on the "Excel Options" button (at the bottom of the menu) Select the "Advanced" Tab on the left Scoll down to the bottom of the page Look for the section "Lotus Compatibility" Check the "Transition Navigation Keys" checkbox Click OK HTH Elkar "joyce" wrote: > how do i get the home key to work. excel 2003 did it...

Startup Template
I have a template (.xltm file) containing a macro that is assigned to a button on the QAT for that template. The macro is in ThisWorkbook. When I just open the template, or double-click it from Windows, the button displays and the macros runs correctly. I want the button available all the time so I saved the template in the Excel start folder - C:\Users\xxxx\AppData\Roaming\Microsoft\Excel\XLSTART. When I launch Excel the button does not display, which I take it to mean the template has not loaded. Same result if I put the template in C:\Program Files\Microsoft Office\Offi...

Excel 2007 chart color change based on the label.
I have 12 charts 1 for each person and the data changes every week and some of the values have 0 so are filtered out. On the chart I want 0-15 to be blue, 16-30 to be light purple, 31-60 to be a cream color, 61-90 to be light blue, and 90+ to be dark purple. With the code below I can change the color if I take the if statement out but I would like to change the color based on the label. I have tried several things like .text, .name, and .label. I just can't fine the right name. Name1 0-15 1 61-90 1 90+ 3 Name 2 0-15 14 16-30 5 31-60 1 61-90 6 90+ 12 For i = 1 To A...

Typing the date into Excel
Hi, and thanks in advance. I've recently updated from Excel 2003 to Excel 2007. I'm having a problem with entering dates: (1) I enter a date, say: 5/17/2009 or 5-17-2009 (2) The cell displays 0.000146 or -2021. (3) I format the cells as "date". (4) The cell displays as 1900/01/00 or ##### (indefinite) (5) The formula bar displays =5/17/2009 or =5-17-2009 (6) It doesn't matter if I format the cells as dates in advance or after entering the date. I'm guessing that there is a setup option somewhere that is wrong, but I can't find it. The documentation, and my ...

Searching for variables in excel database
Hi, I have an excel database ( 10 columns x 1500 rows ) and I want to be able to have a search function on a seperate worksheet. I want the search function to work like the autofilter function, but this way could be more appealling to the eye. Any tips on how to achieve this ??? Thanks -- Gavin77 ------------------------------------------------------------------------ Gavin77's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25824 View this thread: http://www.excelforum.com/showthread.php?threadid=392161 ...

Sending SMS from Excel
Hi, is there any way to send sms from exel ? I wish to send sales information to manegement via SMS. regards Peter Piotr wrote: > Hi, > is there any way to send sms from exel ? > I wish to send sales information to manegement via SMS. > > regards > Peter > There is definitly no function for that. But you could send out a HTTP-Request to a GSM-Sender. Or smth. like that. ...