Stripping out unwanted date in cell

Hello,
I am trying to import a Global address list, but my problem is I need just
the smtp address in the cell so i want to strip out all the other junk, here
is a couple of examples of what the field could appear like. I need to strip
out everything accept for the SMTP:someaddress@someplace.someextension. It
could be any extension and it is not always a percent at the beginning or
end.
SMTP:bubba.cairn@bla.ca%MS:UOF9/UNIVERSITY/bcairn%X400:c=US;a=;p=Uof9;o=Univ
ersity?Hall;s=Cairn;g=bubba;%CCMAIL:Cairn, bubba at University_Hall

CCMAIL:ssdsexam at
STUDENT-SERV%MS:UOF9/STUDENTSER/SSDSEXAM%SMTP:ssds.exams@uss.someplace.ca%X4
00:c=US;a= ;p=Uof9;o=STUDENT-SERV;s=ssdsexam;

Any help is much appreciated.

Sincerely

Jim Kiddoo


0
jkiddoo (5)
10/16/2003 4:18:24 PM
excel 39879 articles. 2 followers. Follow

5 Replies
622 Views

Similar Articles

[PageSpeed] 16

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

    
=MID(A1,FIND("SMTP",A1),MIN(IF(ISERR(FIND({"%",":",";","$"},A1,FIND("
SMTP",A1)+5)),"",FIND({"%",":",";","$"},A1,FIND("SMTP",A1)+5)))-FIND(
"SMTP",A1))

Add any other delimiters to both constant arrays, {...}'s, in the 
same order.

In article <upvl3FAlDHA.2776@tk2msftngp13.phx.gbl>,
 "Jim Kiddoo" <jkiddoo@ualberta.ca> wrote:

> Hello,
> I am trying to import a Global address list, but my problem is I need just
> the smtp address in the cell so i want to strip out all the other junk, here
> is a couple of examples of what the field could appear like. I need to strip
> out everything accept for the SMTP:someaddress@someplace.someextension. It
> could be any extension and it is not always a percent at the beginning or
> end.
> SMTP:bubba.cairn@bla.ca%MS:UOF9/UNIVERSITY/bcairn%X400:c=US;a=;p=Uof9;o=Univ
> ersity?Hall;s=Cairn;g=bubba;%CCMAIL:Cairn, bubba at University_Hall
> 
> CCMAIL:ssdsexam at
> STUDENT-SERV%MS:UOF9/STUDENTSER/SSDSEXAM%SMTP:ssds.exams@uss.someplace.ca%X4
> 00:c=US;a= ;p=Uof9;o=STUDENT-SERV;s=ssdsexam;
> 
> Any help is much appreciated.
> 
> Sincerely
> 
> Jim Kiddoo
> 
>
0
jemcgimpsey (6723)
10/16/2003 6:49:36 PM
Thanks very much, works perfectly unless the smtp address is tha last in the
string, with nothing after it. Any suggestions?
Thanks again
Sincerely
Jim Kiddoo



"J.E. McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-FFE6D9.12493616102003@msnews.microsoft.com...
> One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):
>
>
> =MID(A1,FIND("SMTP",A1),MIN(IF(ISERR(FIND({"%",":",";","$"},A1,FIND("
> SMTP",A1)+5)),"",FIND({"%",":",";","$"},A1,FIND("SMTP",A1)+5)))-FIND(
> "SMTP",A1))
>
> Add any other delimiters to both constant arrays, {...}'s, in the
> same order.
>
> In article <upvl3FAlDHA.2776@tk2msftngp13.phx.gbl>,
>  "Jim Kiddoo" <jkiddoo@ualberta.ca> wrote:
>
> > Hello,
> > I am trying to import a Global address list, but my problem is I need
just
> > the smtp address in the cell so i want to strip out all the other junk,
here
> > is a couple of examples of what the field could appear like. I need to
strip
> > out everything accept for the SMTP:someaddress@someplace.someextension.
It
> > could be any extension and it is not always a percent at the beginning
or
> > end.
> >
SMTP:bubba.cairn@bla.ca%MS:UOF9/UNIVERSITY/bcairn%X400:c=US;a=;p=Uof9;o=Univ
> > ersity?Hall;s=Cairn;g=bubba;%CCMAIL:Cairn, bubba at University_Hall
> >
> > CCMAIL:ssdsexam at
> >
STUDENT-SERV%MS:UOF9/STUDENTSER/SSDSEXAM%SMTP:ssds.exams@uss.someplace.ca%X4
> > 00:c=US;a= ;p=Uof9;o=STUDENT-SERV;s=ssdsexam;
> >
> > Any help is much appreciated.
> >
> > Sincerely
> >
> > Jim Kiddoo
> >
> >


0
jkiddoo (5)
10/16/2003 9:45:15 PM
This has gotten convoluted enough to make me think there's a better 
way, but this will work (again, array-entered):

=MID(A1,FIND("SMTP",A1), MIN(IF(ISERR(FIND({"%",":",";","$"}, A1, 
FIND("SMTP",A1)+5)), "", FIND({"%",":",";","$"} , A1, 
FIND("SMTP",A1)+5))) + ((LEN(A1)+1) * ISERR(FIND({"%",":",";","$"}, 
A1, FIND("SMTP",A1)+5))) - FIND("SMTP", A1))

In article <uEfrh8ClDHA.1948@TK2MSFTNGP12.phx.gbl>,
 "Jim Kiddoo" <jkiddoo@ualberta.ca> wrote:

> Thanks very much, works perfectly unless the smtp address is tha last in the
> string, with nothing after it. Any suggestions?
0
jemcgimpsey (6723)
10/16/2003 11:56:05 PM
But we all admire your tenacity.
<vbg>

"J.E. McGimpsey" wrote:
> 
> This has gotten convoluted enough to make me think there's a better
> way, but this will work (again, array-entered):
> 
> =MID(A1,FIND("SMTP",A1), MIN(IF(ISERR(FIND({"%",":",";","$"}, A1,
> FIND("SMTP",A1)+5)), "", FIND({"%",":",";","$"} , A1,
> FIND("SMTP",A1)+5))) + ((LEN(A1)+1) * ISERR(FIND({"%",":",";","$"},
> A1, FIND("SMTP",A1)+5))) - FIND("SMTP", A1))
> 
> In article <uEfrh8ClDHA.1948@TK2MSFTNGP12.phx.gbl>,
>  "Jim Kiddoo" <jkiddoo@ualberta.ca> wrote:
> 
> > Thanks very much, works perfectly unless the smtp address is tha last in the
> > string, with nothing after it. Any suggestions?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
10/17/2003 1:44:41 AM
That worked perfectly! Thank you very much, you have made my life much
easier. Thanks Again!
Sincerely
Jim Kiddoo


"J.E. McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-13F613.17560516102003@msnews.microsoft.com...
> This has gotten convoluted enough to make me think there's a better
> way, but this will work (again, array-entered):
>
> =MID(A1,FIND("SMTP",A1), MIN(IF(ISERR(FIND({"%",":",";","$"}, A1,
> FIND("SMTP",A1)+5)), "", FIND({"%",":",";","$"} , A1,
> FIND("SMTP",A1)+5))) + ((LEN(A1)+1) * ISERR(FIND({"%",":",";","$"},
> A1, FIND("SMTP",A1)+5))) - FIND("SMTP", A1))
>
> In article <uEfrh8ClDHA.1948@TK2MSFTNGP12.phx.gbl>,
>  "Jim Kiddoo" <jkiddoo@ualberta.ca> wrote:
>
> > Thanks very much, works perfectly unless the smtp address is tha last in
the
> > string, with nothing after it. Any suggestions?


0
jkiddoo (5)
10/17/2003 3:46:38 PM
Reply:

Similar Artilces:

Pivot tables not keeping date ranges in order
Hi all, I have a spreadsheet that shows the date that quotes were created. I want to create a pivot table that just summarizes the number of quotes that were created in a given month-year format. First I changed all the "created on" dates to month/1/year format (where "1" is constant) instead of month/day/year (where "day" is the specific day the quote was created: 1, 3, 4, 8, 15, 23, etc.). Then I created a pivot table off that, but the pivot table doesn't keep the months in order. I have the "created on" field in the row field of the pivot t...

Date Calculations, Corss Tab?
Hello! First of all, thank you in advance for taking the time to help me! I am trying to create queries that will show future revenues, for invoices being amortized over defined periods, to be grouped by months for the first twelve months (from a date selected), and the remainder in a “+12” column. This report would be similar to an AR Aging that puts an invoice in to 30, 60, 90, +120 buckets. The amounts are stored in my table “tblRA – Amortization”, the Report date (“the date selected”) will be user defined and entered on a form. As if this is not complicated enough, depending on...

Stripping a line #3
yeah splitting them at the @ symbol. Could you be a bit more specific? -- insane ------------------------------------------------------------------------ insane's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15743 View this thread: http://www.excelforum.com/showthread.php?threadid=272588 But in your example you DIDN'T split at every @ sign: the 2nd cell contained them. On Tue, 26 Oct 2004 15:32:19 -0500, insane <insane.1er3sm@excelforum-nospam.com> wrote: > >yeah splitting them at the @ symbol. > >Could you be a bit more specific? ...

Change payroll posted date?
Is there a way to change the payroll posted date if it has not been posted to GL? What do you mean that it has not been posted to GL ? What steps have you completed in Payroll? -- Charles Allen, MVP "ts" wrote: > Is there a way to change the payroll posted date if it has not been posted to > GL? > > We have printed the checks and completed the "posting" for the payroll process but we do not post through to the GL. I thought there may be an opportunity at this point to change the date that we had entered on the payroll posting screen. Basical...

Cell being excluded from range
I scanned my credit card bill to incorporate the entries into my Excel personal finance file. I was somewhat alarmed to note (after spending some time trying to find out what had gone wrong) to note that the sum of a column of figures did not include a particular one of them. I noticed this because I had copied the cell across to another column and it did not appear in the total of that column. Only when I deleted and entered it in by hand did it show up. I thought that maybe it had got itself entered as a text entry, but this did not seem to be the case. Can someone suggest why this i...

Getting a cell address and using it
I have a spreadsheet containing production information. I want to b able to search by date (can do this using VLOOKUP), and extrac information from the same row as that date. However, I also want to be able to extract the previous 10 entries a well, for comparison purposes. ie I enter a date and find that it is in B95, and the piece of info want is in C95. I also want to be able to pull out C86-94 and place i all into a new sheet. I'd be grateful for any help offered. Thank -- dudar ----------------------------------------------------------------------- dudara's Profile: http://w...

Excel 2000
Hello I am new and exhausted from trying to find an answer to m problem. I enter a date into a cell (ex. 020206 which should auto format t 02/02/2006) but when I hit enter I get a weird date that comes u 1/23/7436. This happens with any date I enter. I tried to custom format the cel ##/##/## but that doesn't work, and I tried "text to columns" but ha no success. Any help with detailed solutions would be appreciated. thank -- vt ----------------------------------------------------------------------- vtz's Profile: http://www.excelforum.com/member.php?action=getinfo&us...

Calculating date/time intervals??
I have multiple sets of date/time value combinations. For each start time (date/time) and finish time (date/time), I need a formula that calculates the number of days, hours, and minutes between the two events. Example: Start 5/14/2010, 11:25 Finish 5/17/2010, 18:30 Thanks for any assistance! John Hi, Assume that starting entry is in cell C7 and finishing entry is in cell C9 =INT(C9)-INT(C7) for days; =(C9-C7)*24*60 for minutes; and =(C9-C7)*24 for days -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "jj" <jj@discussions.micr...

Combining text and cell value to create a sentence
In Excel 2000 I have something like the following: A B C D Inc/Dec 1 April May YTD Goal 2 $75 $50 $10 $200 decreased I'd like to form a sentence such as Recoveries decreased from May to April. I've tried "="Recoveries " &Text(E2) " from" &Text(B1) "to " &Text(A1) But keep getting "too many arguments". Any suggestions, or is this possible? Simply =Recoveries " & E2 &" from "& B1 & " to " & A1 the T...

If cell equals, give total of another cell #2
I have a formula that looks likes this: =IF(Historical!I10="X","Historical!A10","") If true, it returns "Historical!A10" How do I write this formula to give me the value of A10 and not the name of the cell? -- Lori =IF(Historical!I10="X",Historical!A10,"") Vaya con Dios, Chuck, CABGx3 "Lori Burton" wrote: > I have a formula that looks likes this: > > =IF(Historical!I10="X","Historical!A10","") > > If true, it returns "Historical!A10" > > How do I writ...

How to create a formula that results in a blank cell if required
I have a spreadsheet that is to be populated during the course of the year. I have a column which totals the number of days worked on a particular task and have copied the formula down the sheet for the number of entries required. Obviously, as the sheet is currently unpopulated, I do not want a whole load of zeroes listed in this column when the other cells in the sheet (which are used in the calculation) are currently empty. ie) what I have is (as a simple example) A B C ------------------------------- Hours Total Hrs Total Days 37 37 5 ...

ISO SQL Date Format / ISO Date Formats
ISO date formats come in a number of variations, two such variations are YYYY-MM-DD and YYYYMMDD. The ISO SQL Standard date format is YYYY-MM-DD only which is termed a "date literal". According to the standard (see here: http://savage.net.au/SQL/sql-92.bnf.html#date literal) the "date literal" in order for it to be a "date literal" MUST be prefixed by the keyword DATE, if not then the text is simply nothing more than a "string literal" which has different meaning and subject to regional settings if used on data type conversions ie. st...

Stripping hyperlinks
Word 2007 inexplicably started stripping all hyperlinks, email addresses and other automated information a few months ago, and I can't find anything in the "Wor Options" sections to make it stop. I didn't ask it to do this, nor have I made any changes in the settings prior to when this started happening. As an example, a link such as www.yahoo.com will appear as {HPERLINK "http://www.yahoo.com"} Please help!! Press Alt+F9 to hide field codes. -- Stefan Blom Microsoft Word MVP "jlavietes" <jlavietes@discussions.microsoft.co...

linking cells for auto inventory depletion
I have an invoice program set up whereby in cells D22 to D40 there is drop down list which comes up where the user chooses a product by it code. In cells B22 to B40 is where they input the quantity being sold In a seperate inventory workbook I have the same product codes an quantities on hand that is looked after everyday. To save on time o having to go in and take out of inventory everything that gets sold, was hoping there is a way to set up some type of coding somewhere tha will in effect match the invoice program product code to the inventor program product code, then once that is matche...

Copying dates between Excel documents
I have noticed that when I copy a date eg/ 23/02/04 (UK format) from on Excel document to another the date becomes 22/02/00. Has anyone comes across this, its really infuriating as I have t redate everything again Thank -- Message posted from http://www.ExcelForum.com XL has two date systems, the Windows default 1900 date system, in which the 0-base date is 31 December 1899, and the MacXL default 1904 date system, in which the 0-base date is 1 January 1904. The systems differ by 4 years and a day. To resolve your issue, make sure that both workbooks have the same date system selected...

custom form to strip attachments and store them
I would like to build a custom form to strip attachments and store them on a server share. Is there any way to do this in Outlook 2000/Exchange 5.5? ...

exe is being stripped from my emails
Lately, since my internet provider has upgraded its mail system, my emails containing test .exe's, which I send to my brother, are being blocked. Their support tech advised me to simply change the extension from .exe to something else, since they screen attachments according to file extensions. However, I have not figured out how to access my executable's extension. Can someone advise me on this problem? Thanks. Outlook does this too. Just ZIP the files before attaching them to your outgoing email. Rail -- Recording Engineer/Software Developer Rail Jon Rogut Sof...

DSum between dates
I have a challenging question here. I am trying to work with values between certain dates, and want to know if there is some code I can add that will sum values between certain dates, but not take into account the year. I want to find averages for seasons, but not seperate the years. So every year the averages would calculate with the previous year. Here are my details: subForm name: AutosGasDataCRVsub Opens on Main form: AutomobileGasolineSearch Name of text box where the value will be: AVGMPGWinter Name of query where information is found: AutosGasStatsCRV I will have seperate text...

Calender Dates 20, 28
I have a calender on my work PC that someone previously formatted. I must continue this calender of work events, but have noticed that when I type the numeric dates 20 or 28 and click off of it, the 20 or 28 changes to #. However, when I print the calender the 20 and 28 are visible, not the #. Can anyone solve this puzzle please. Thanks from a Senior Citizen. l.etheridge Excel is not quite WYSIWYG (What you see is what you get). It sounds like you could widen the column(s) just a hair to see the numbers on the screen. (I've experienced the opposite, too. You can see the numbers, ...

stripping notes
We recorded a conversation in our PDA and successfully transfered to Microsoft Outlook. It is now sitting as a yellow "NOTE". How can I strip the recording and save it as a seperate file in my directory? The recording now appears as a speaker icon inside the note. Thanks Much ...

CHANGE DATES #2
I WANT TO BE ABLE TO TYPE 6 DIGIT NO. EG 010101 AND EXCEL CHANGE IT TO 01/01/01 HOW CAN I DO THIS SIMPLY THANKYOU Hi Either format the cell prior to entering values as 'Text' or enter the number with a preceding apostrophe, e.g. '010101 HTH Frank P.S.: please turn off your CAPS lock as it's quite difficult to read and is considered rude (shouting) in newsgroups ME wrote: > I WANT TO BE ABLE TO TYPE 6 DIGIT NO. EG 010101 AND EXCEL CHANGE IT > TO 01/01/01 HOW CAN I DO THIS SIMPLY THANKYOU I'M SORRY BUT I DON'T UNDERSTAND THAT DOES NOT SEEM TO WORK FOR ME Hi B...

Importing text to chart based on link to cell
I am attempting to link a cell from my spread sheet to a text box in my chart, but have been unable to do so. Does anyone know how or if this is possible. On Fri, 7 Nov 2003 12:59:29 -0800, Stephen <stephen.szyndrowski@telus.co= m> = wrote: > I am attempting to link a cell from my spread sheet to a > text box in my chart, but have been unable to do so. Does > anyone know how or if this is possible. Select the chart, then click in the formula bar (above the chart, next t= o = the little "fx"), type the equal sign, then click on the cell that you = wish to refe...

entering year to date wages
I need to keep track of my wages for the year. I just started money 2004. There does not appear to be a way to enter year to date information from my pay check. Sure there is. Enter a paycheck with all of the YTD information (assuming this is prior to any paychecks you are planning on entering). Then offset the net of this transaction with some offsetting value entry in the after tax split--put a memo in and you can even skip categorizing it. The transaction ends up at $0. The categories all end up getting the YTD values recorded. But I'm hoping I understand your goal. I.e., you are...

Bold individual dates on a yearly calendar
I am trying to create a yearly calendar with only certain dates bolded. How do I "unlock" text so that I can? Select the month, copy, paste special as a table. You will have to re-size the table to fit the space, but now you can bold the dates. Another solution would be to create individual months through the Design Gallery. Each month would have to be re-sized, but the calendar is already in table form. If you have Publisher 2003 the Design Gallery has a wizard wand on the bottom of the calendar month. Once you re-size the calendar month, you can copy/paste and change the d...

Tabbing between cells
I am using Excel 97 (at work). I thought that I could enter the information and then hit TAB to go to the next cell to the right. However, when I hit tab, the cells jump ten cells away! How can this be changed? Can it? Or Do I have to live with it until work updates it? Thanks in advance! Kathy L Los Angeles I can say it's not Excel 97 per say, because I use Excel 97 at work as well. A thought. Have you checked 'Tools->Options->Transition-> Untick "Transition Navigation Keys" with Microsoft Excel Menus That's the only thing I can find so far. But I...