shortening text in cells

[Guest]

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:

(e-mail address removed)#mailto:Spot

and change to:

(e-mail address removed)

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]

 

[Guest]

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

 

[Ron Rosenfeld]

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:

(e-mail address removed)#mailto:Spot

and change to:

(e-mail address removed)

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

 

[Guest]

Thank you all. It worked great!