I have numerous emails that have an additional #mailto:blahblahblah on the
end. Is there a way to write a macro or use a formula that would remove
everything starting with the # sign?

For example:

#mailto:Spot

and change to:



Each cell, starting at F2, has a different length of characters after the #.

Thanks in advance.

Hi srroduin,

There are a few ways you can do this without VBA code:

1) Use Text to Columns: Select Data | Text to Columns, Delimited, put # in
other, Next, select col 2 then select "Do not import column (skip)", Finish.

2) Use a formula like this:

=LEFT(F2,IF(ISERROR(SEARCH("#",F2)),LEN(F2),SEARCH ("#",F2)-1))

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]


srroduin wrote:
I have numerous emails that have an additional #mailto:blahblahblah
on the end. Is there a way to write a macro or use a formula that
would remove everything starting with the # sign?

For example:

#mailto:Spot

and change to:



Each cell, starting at F2, has a different length of characters after
the #.

Thanks in advance.

Here is another way, but either way you will have to use two cells. Whereas
if you use code then you can loop through your range and edit the contents
and place them in the same cell again.

=MID(A4,1,FIND("#",A4,1)-1)

Chas

"srroduin" wrote:

I have numerous emails that have an additional #mailto:blahblahblah on the
end. Is there a way to write a macro or use a formula that would remove
everything starting with the # sign?

For example:

#mailto:Spot

and change to:



Each cell, starting at F2, has a different length of characters after the #.

Thanks in advance.

On Thu, 10 Aug 2006 14:34:02 -0700, srroduin
wrote:

I have numerous emails that have an additional #mailto:blahblahblah on the
end. Is there a way to write a macro or use a formula that would remove
everything starting with the # sign?

For example:

#mailto:Spot

and change to:



Each cell, starting at F2, has a different length of characters after the #.

Thanks in advance.

Perhaps something like this will get you started:

=============================
Option Explicit
Sub TrimEmail()
Dim c As Range
Dim i As Long

For Each c In Selection
i = InStr(1, c.Text, "#")
If i 0 Then
c = Left(c.Text, i - 1)
End If
Next c
End Sub
=======================


--ron

Thank you all. It worked great!

"srroduin" wrote:

I have numerous emails that have an additional #mailto:blahblahblah on the
end. Is there a way to write a macro or use a formula that would remove
everything starting with the # sign?

For example:

#mailto:Spot

and change to:



Each cell, starting at F2, has a different length of characters after the #.

Thanks in advance.