How to Excel (or Access) return thcharacters up to a certain chara

J

JustALittleHelp

I need to separate email addresses and require all characters up to the @
sign. I seem to recall a count required, and then subsequent formula on that
return that dynamically incorporates the count into a Left formula?
 
J

JustALittleHelp

Just after I wrote this question I haphazarded a solution in Excel. Yours
looks the same, but I used FIND (Excel version 2000), you've used SEARCH
(Access?), I completed the task thus:

added 4 columns to spreadsheet (essentially beside email address).

column 1(E): =FIND(find_text,within_text,start_num) -> gets the char's
position
column 2(F): =LEN(text) -> length of cell's text
column 3(G): =+F1-E1 -> total length of cell's text minus char's position
(gets # chars right of char in question (in this case '@' of an email
address)
column 4(H): =RIGHT(text,num_chars) - pulls only those characters to the
right of the '@' symbol.

Is there a shorter way? I'm sure this wont be the first time I'll need to do
this. In both Excel, or Access.

Thanks!
 
B

Bob I

Pack it all into one cell

What do you want? The right half or the left half?

Left side is

LEFT(e-mailaddress,FIND("@",e-mailadddress)-1)

Right side is

MID(e-mailaddress,FIND("@",e-mailadddress),30)

Works in Access also.
 
B

Beth Melton

If you're using Excel then use Data/Text to Columns and in the
Delimiters use the Other text box (step 2) and type the @. Excel will
do the rest for you. :)

Please post all follow-up questions to the newsgroup. Requests for
assistance by email can not be acknowledged.

~~~~~~~~~~~~~~~
Beth Melton
Microsoft Office MVP

Office 2007 Preview Site:
http://www.microsoft.com/office/preview/default.mspx
Office 2007 Community Articles/Tutorials:
http://www.microsoft.com/office/preview/community/article_archive.mspx

TechTrax eZine: http://mousetrax.com/techtrax/
MVP FAQ site: http://mvps.org/
 
Top