Text to Columns Macro

FinancialAnalystKid

FinancialAnalystKid

Well-known Member
Joined
Oct 14, 2004
Messages
779
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!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Does the URL always start with http?
 
Upvote 0
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
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
On my keyboard it's the same button as the 6. But any unique character in the column is fine... :-)
 
Upvote 0
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
You must log in or register to reply here.

Similar threads

V
  • Question Question
Searching for text in textboxes in Excel
2
Replies
15
Views
508
DanteAmor
DanteAmor
A
  • Question Question
How can I create a button to insert a new row and update related formulas?
Replies
0
Views
157
Arlochorim
A
A
  • Question Question
VBA Split semicolon and comma separated values into new rows
Replies
9
Views
640
Adalinda
A
N
  • Question Question
Trying to extract URL and get it in correct order
Replies
3
Views
116
Artik
Artik
L
  • Question Question
Excel cell format change without conditional formatting
Replies
5
Views
144
mumps
mumps

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top