Text to Columns Macro

FinancialAnalystKid

Well-known Member

Joined

Oct 14, 2004

Messages

779

• Oct 10, 2011

• #2

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

 

Upvote 0

W

wigi

Well-known Member

Joined

Jul 10, 2006

Messages

7,964

Office Version

1. 365

Platform

1. Windows
2. Mobile
3. Web

• Oct 10, 2011

• #3

Does the URL always start with http?

 

Upvote 0

W

wigi

Well-known Member

Joined

Jul 10, 2006

Messages

7,964

Office Version

1. 365

Platform

1. Windows
2. Mobile
3. Web

• Oct 10, 2011

• #4

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

Code:

Sub splitit()

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

End Sub

 

Upvote 0

FinancialAnalystKid

Well-known Member

Joined

Oct 14, 2004

Messages

779

• Oct 10, 2011

• #5

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!

 

Upvote 0

FinancialAnalystKid

Well-known Member

Joined

Oct 14, 2004

Messages

779

• Oct 10, 2011

• #6

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

 

Upvote 0

W

wigi

Well-known Member

Joined

Jul 10, 2006

Messages

7,964

Office Version

1. 365

Platform

1. Windows
2. Mobile
3. Web

• Oct 10, 2011

• #7

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

 

Upvote 0

W

wigi

Well-known Member

Joined

Jul 10, 2006

Messages

7,964

Office Version

1. 365

Platform

1. Windows
2. Mobile
3. Web

• Oct 10, 2011

• #8

Here you go:

Code:

Sub splitit()

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

End Sub

Only the selection will be done.

 

Upvote 0

FinancialAnalystKid

Well-known Member

Joined

Oct 14, 2004

Messages

779

• Oct 10, 2011

• #9

Hey! Thanks!!!

 

Upvote 0