Remove partial data within each Row, HOW??

[excelboxnovice]

I am very new to Excel so please excuse the limited knowledge of asking this question within this community.



I have a Column that contains website in each row. I need to remove ALL details except the final domain.


Basically, only need domain data AFTER the following phrase: url=


Some ROWs include the WWW while others simply have the domain. Note. Each ROW is different domain, the only element the same is the url=


Here is examples. note: I have changed the domain/url to respect the privacy.


COLUMN A:


A1: http://www.originalsource.com/goto.<wbr>php?type=l&agtid=129&url=www.<wbr>domain1.com; (only need www.domain1.com)

A2: http://www.originalsource.com/goto.<wbr>php?type=l&agtid=130&url=www.<wbr>domain2.com;
(only need www.domain1.com)

A3: http://www.originalsource.com/goto.<wbr>php?type=l&agtid=136&url=domain3.com
(only need domain3.com


etc





I look forward to your reply and thank you in advance for your feedback.


Please try to keep the answers very detailed (step by step) because of my limited excel knowledge.

 

[pike]

Hi excelboxnovicein,
In cell B1 enter the formula =RIGHT(A1,LEN(A1)-3-FIND("url=",A1))
and fill the formula down the desired number of cells

 

[excelboxnovice]

This works when only one domain is in the row, however sometimes in the rows contain multiple urls.

A!: http://www.originalsource.com/goto.<wbr>php?type=l&agtid=130&url=www.<wbr>domain2.com; http://www.originalsource.com/goto.<wbr>php?type=l&agtid=130&url=www.<wbr>domain2.com;

Usually its simply a repeat but its still showing in the formula you provided. Please advise.


BTW: I would greatly appreciate feedback/advice on the two following question I posted earlier.

1. http://www.mrexcel.com/forum/excel-questions/926252-column-row-move-some-row-data-how.html

2. http://www.mrexcel.com/forum/excel-...ing-arranging-columns-incorrect-row-data.html

 

[ww4612]

can you make an example of multy domain url? are those domain connected with ";"?
do you mean that there can be more than one url in a cell? or there are more than one domain in each url?

 

[ww4612]

put this code in a module

Function dom(ByVal os As String) As String
Dim s As Variant, v As Variant, i As Integer
s = Split(os, "http://")

ReDim v(1 To UBound(s))
For i = 1 To UBound(s)
    v(i) = Split(s(i), "url=")(1)
    v(i) = Split(v(i), ";")(0)
    If dom = NullString Then
        dom = v(i)
    Else
        dom = dom & ";" & v(i)
    End If
Next i
End Function

use this formula
=dom(cell reference)

 

[excelboxnovice]

As the example showed. url and then ";" with another url and possibility another ";" all within the same cell.

if you have any other questions let me know.

 

[Akuini]

Maybe this:

Sub a926268()
Dim r As Range
Dim arr1

For Each r In Range(Range("A1"), Range("A" & Cells.Rows.count).End(xlUp))
arr1 = Split(r, "url=")
r.Offset(0, 1) = arr1(UBound(arr1))
Next

End Sub

 

[excelboxnovice]

Thanks for everyone advising, I will attempt all the suggestions today and let you know the results soon. Any other feedback would be helpful with these two other postings..

http://www.mrexcel.com/forum/excel-questions/926249-sorting-arranging-columns-incorrect-row-data.html

http://www.mrexcel.com/forum/excel-questions/926252-column-row-move-some-row-data-how.html