Text to Columns Macro

[FinancialAnalystKid]

I have a spreadsheet with text and an url in the same cell. Some have an url and some don't. I need to create a new column with the url and delete it from the original cell so I can have 2 cells.

Any help would be greately appreciated!

 

[FinancialAnalystKid]

The URL is at the tail end of cell and not in middle if that helps.

 

[wigi]

Does the URL always start with http?

 

[wigi]

Assuming yes as the answer to my previous question, try...

Sub splitit()

    With Columns(1)
        .Replace "http:", "§", xlPart
        .TextToColumns OtherChar:="§"
        .Offset(, 1).Replace "//", "http://"
    End With

End Sub

 

[FinancialAnalystKid]

Yes. It contains "http"

I need to highlight the cells say from a2:a200 and run the macro. Is that possible? I tried the attached code and it didn't work. It replaced http with §.

Which gave me an idea. To do a FIND REPLACE http with §http
Then Text to Columns on §

Which takes steps but if a Macro won't do, your code gave me that idea! Thanks

If you do have a code that would be great!

 

[FinancialAnalystKid]

off topic: what is the keyboard strokes for § anyway?

 

[wigi]

On my keyboard it's the same button as the 6. But any unique character in the column is fine...

 

[wigi]

Here you go:

Sub splitit()

    With Selection
        .Replace "http", "§http", xlPart
        .TextToColumns other:=True, OtherChar:="§"
    End With

End Sub

Only the selection will be done.

 

[FinancialAnalystKid]

Hey! Thanks!!!