truncating data before hyphen

**Wallfly** · 10-25-2012, 03:44 PM

Hi,

I was an expert Lotus user! Which belies my rustiness and age. I have rudimentary Excel skills--understand formulas but have never been able to get a macro to work! My version of Excel is 2011 for Mac. I don't think the Mac version is relevant here. My problem:

I have around 1000 cells that have a web address then an email address separated by a space then a hyphen then another space. The problem is that there aren't a consistent no. of characters in either the web address or the email. If there were I think I could use the Left function. I only want the email address so it's a matter of getting rid of everything before the hyphen and the space following space. Note: these are not live links in the spreadsheet or here. Thanks!!!!

Examples:

www.eeginfo.com*- [email protected]
www.alaskaneurofeedback.com*- [email protected]
www.affordablecounselingsolutions.com*- [email protected]

**Wallfly** · 10-25-2012, 03:46 PM

I'm sorry--I made a typo. it should be "it's a matter of getting rid of everything before the hyphen and the space following the hyphen."

**jeffreybrown** · 10-25-2012, 03:47 PM

With your first value in A1...

=MID(A1,FIND("-",A1)+2,LEN(A1))

...copied down

**Wallfly** · 10-25-2012, 04:05 PM

It worked!! (by putting the formula in B1.)

Jeff-An additional favor: would you mind explaining the syntax? I think it will help me learn how to work with other formulae. As far as I can get--"In the middle of the contents of A1 find a hyphen, then move 2 to the right then LEN. Why do I use LENin this case, and do I have the rest right?

Thanks!

**jeffreybrown** · 10-25-2012, 06:27 PM

The MID function has three arguments.

=MID(text,start_num,num_chars)

The first argument is easy, what cell are you evaluating, and in this case, A1.

A1 = www_eeginfo.com* - sjacobs_knology.net

The next argument looks for a place to start. We use the "-" as the delimiter and then add two to establish our start number,

=MID("www_eeginfo.com* - sjacobs_knology.net",19,LEN(A1))

Finally, we use the Len(A1) to return the original length of the text to ensure the function stretches out enough numbers to capture the portion we are looking for. I use the Len just because it's what I learned, but some people will replace it with a big number such as 255. There may be other approaches, but as you testified, this works for you.

These both do the same job:
=MID(A1,FIND("-",A1)+2,LEN(A1))
=MID(A1,FIND("-",A1)+2,255)

In the end it all evaluates too...

=MID("www_eeginfo.com* - sjacobs_knology.net",19,37)

A great way to see what a formula is doing is to use the Evaluate Function.

I know nothing about a MAC so not sure how to find this feature on a MAC.

**jeffreybrown** · 10-25-2012, 06:34 PM

You are very welcome and thanks for the feedback

-------------------------------------------------------------------------------------

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save