shortening text in cells
 

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.

shortening text in cells
 

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.

shortening text in cells
 

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.

shortening text in cells
 

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

shortening text in cells
 

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.