Solved: Company Name and Full URL Separation

[freaknyea]

Right now I have the company name and full URL in one cell. It looks like this:

A1

COMPANY NAME http://www.companyname.com

A2
COMPANY NAME 2 http://www.companyname2.com

A3
COMPANY NAME 3 http://www.companyname3.com


I need to separate the Company name and URL. How do I do that? I want it to look like:

A1 __________________ B1
COMPANY NAME http://www.companyname.com

A2 _________________ B2
COMPANY NAME 2 http://www.companyname2.com

A3 ________________ B3
COMPANY NAME 3 http://www.companyname3.com


Thanks!

[Simon Lloyd]

Do you want a formula solution?
In B1: =LEFT(A1,FIND("http",A1,1)-1)
in C1: =RIGHT(A1,FIND("http",A1,1)+FIND("http",A1,1))

If you want a VBA solution, this is crude but does the job:
[VBA]Sub Split_Url()
Dim Rng As Range, MyCell As Range
Set Rng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each MyCell In Rng
MyCell.Offset(0, 1).Value = "=Left(" & MyCell.Address & ",FIND(" & """http""" & "," & MyCell.Address & ",1)-1)"
MyCell.Offset(0, 2).Value = "=RIGHT(" & MyCell.Address & ",FIND(" & """http""" & "," & MyCell.Address & ",1)+FIND(" & """http""" & "," & MyCell.Address & ",1))"
Next MyCell
Columns("B:C").Value = Columns("B:C").Value
Columns(1).Delete
Columns("B:C").AutoFit
End Sub[/VBA]

[mdmackillop]

A UDF? Enter =CONAME(A1)
[VBA]
Function CoName(Data As Range)
CoName = Split(Data, "www.")(1)
CoName = Split(CoName, ".")(0)
End Function

[/VBA]

[Bob Phillips]

Joiining in

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow 'LastRow to 1 Step -1

With .Cells(i, "A")

.Offset(0, 1).Value2 = Right$(.Value2, Len(.Value2) _
- InStr(.Value2, "http") + 1)
.Value2 = Left$(.Value2, InStr(.Value2, "http") - 2)
End With
Next i
End With
End Sub
[/vba]