Forum Home
Press F1
 
Thread ID: 45397 2004-05-21 03:51:00 Making a Excel Macro/Filter nz_liam (845) Press F1
Post ID Timestamp Content User
238121 2004-05-21 03:51:00 Hi,

I have an excel sheet full of addresses like bob@jhons-domain.com, and I would like to make a macro/filter that removes the "bob@" part of the address leaving only the “johns-domain.com” part.

So far what I have tried doing is starting the recording, then deleting the "bob@" part of the address, but when I run the macro on the next address it simply pastes "johns-domain.com" over the existing address, instead of deleting the "name@" part of the address.

I know its just a simple fix, its just the method that I’m using is incorrect; any suggestions?

Regards

nz_liam 		nz_liam (845)
238122 2004-05-21 06:22:00 I've written a quick function below that does the job. Note that you may like to make it more intelligent re the addressing of cells. The cell references here are to "Sheet1", column 1, and rows 1 to 3. All these parameters you can change to suit your needs.


Public Sub StripDomainAddress()

Dim colIndex As Integer
Dim rowIndex As Integer
Dim strTemp As String

'Insert your column number here: A = 1
colIndex = 1

'Cycle through each of your rows
For rowIndex = 1 To 3
'change your sheet name if appropriate
With Worksheets("Sheet1").Cells(rowIndex, colIndex )
strTemp = .Value
'Check to see if the @ symbol is present
If InStr(strTemp, "@") > 0 Then
.Value = Right(strTemp, Len(strTemp) - InStr(strTemp, "@"))
End If
End With
Next rowIndex

End Sub


Cheers
Dave 		odyssey (4613)
238123 2004-05-21 06:40:00 Assuming that the address is in Cell A1 and going down then use the following formula in Cell B1

=RIGHT(A1,LEN(A1)-SEARCH("@",A1,1))

Copy the formula down as far as the cells you want to convert.

Much faster than using a macro. 		Big John (551)
238124 2004-05-21 06:49:00 Hey,

Thanks for the help guys :), In the end I just used the formula =RIGHT(C2,LEN(C2)-SEARCH("@",C2,1)) instead of breaking out the macro editor and the VB.

All sorted and working well :):) 		nz_liam (845)
238125 2004-05-21 06:53:00 I would have used the data, text to columns function, using @ as the criteria to split the cells wotz (335)
238126 2004-09-08 12:10:00 > Assuming that the address is in Cell A1 and going
> down then use the following formula in Cell B1
>
> =RIGHT(A1,LEN(A1)-SEARCH("@",A1,1))
>
> Copy the formula down as far as the cells you want to
> convert.
>
> Much faster than using a macro.


Hey, I need to use the formula again, but this time instead of turning "bob@bob'sdomain.com" into "bob'sdomain.com". I need to turn it into "bob" and remove the 'bob'sdomain.com' bit, while adding @xyz.net.

So "bob@bob'sdomain.com" becomes “bob@xyz.net"


Kind Regards

Liam 		nz_liam (845)
238127 2004-09-08 12:26:00 Try this :

=LEFT(A1,SEARCH("@",A1))&"xyz.net"

Andrew 		andrew93 (249)
238128 2004-09-12 23:36:00 Another Excel Question, how can I get it to take the number in cell A2 and add '5' or '5%', whichever is larger?

Cheers

Liam 		nz_liam (845)
238129 2004-09-13 00:18:00 =IF(A2*0.05>5,A2+(A2*0.05),A2+5) parry (27)
238130 2004-09-13 00:40:00 > =IF(A2*0.05 > 5,A2+(A2*0.05),A2+5)


Cheers, Works perfectly! :D 		nz_liam (845)
1 2 3