shortening text in cells

[srroduin]

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:

mydogspot@doggy.com#mailto:Spot

and change to:

mydogspot@doggy.com

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

Thanks in advance.

[Jake Marx]

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:
>
> mydogspot@doggy.com#mailto:Spot
>
> and change to:
>
> mydogspot@doggy.com
>
> Each cell, starting at F2, has a different length of characters after
> the #.
>
> Thanks in advance.

[ChasAA]

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:
>
> mydogspot@doggy.com#mailto:Spot
>
> and change to:
>
> mydogspot@doggy.com
>
> Each cell, starting at F2, has a different length of characters after the #.
>
> Thanks in advance.

[Ron Rosenfeld]

On Thu, 10 Aug 2006 14:34:02 -0700, srroduin
<srroduin@discussions.microsoft.com> 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:
>
>mydogspot@doggy.com#mailto:Spot
>
>and change to:
>
>mydogspot@doggy.com
>
>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

[srroduin]

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:
>
> mydogspot@doggy.com#mailto:Spot
>
> and change to:
>
> mydogspot@doggy.com
>
> Each cell, starting at F2, has a different length of characters after the #.
>
> Thanks in advance.