split cell contents with no delimiter

M

maheshr68

Board Regular
Joined
Sep 25, 2009
Messages
68
Greetings!

IN an excel sheet have many entries as below-

Code: 
Row 1 http://www.uploadstation.com/file/aqyzfzt
 Row 2 http://cramit.in/7ejbvpkg9q0z
Row 3 http://www.uploadstation.com/file/aqyzfzthttp://img607.imageshack.us/img607/8489/31989649.jpghttp://leetleech.org/images/34638303188090584995.gifcreditsscreenshttp://img196.imageshack.us/img196/2710/screenft.jpgdvd9teamtntfilesizesourcehttp://img839.imageshack.us/img839/5092/2011.jpgdvdrip2011widthcodecvideoduration56mn
 Row 4 http://cramit.in/7ejbvpkg9q0zhttp://www.uploadstation.com/file/aqyzfzthttp://img607.imageshack.us/img607/8489/31989649.jpghttp://leetleech.org/images/34638303188090584995.gifcreditsscreenshttp://img196.imageshack.us/img196/2710/screenft.jpgdvd9teamtntfilesizesourcehttp://img839.imageshack.us/img839/5092/2011.jpgdvdrip2011widthcodecvideoduration56mn
 Row 5 http://oron.com/duq3g9xvnci6/..2011.1CD.DVDRip.XviD.TeamTNT.avi.html
Row 6 http://www.uploadstation.com/file/EEjYyn6/..2011.1CD.DVDRip.XviD.TeamTNT.avi
 Row 7 http://www.fileserve.com/file/yjjtjtb/.2011.1cd.dvdrip.avihttp://i.imgur.com/xfvsn.jpghttp://www.duckload.com/download/6110631/.2011.1cd.dvdrip.avihttp://videobb.com/f/dedxm7fgqdxo.swfhttp://maxupload.net/images/44385022067938883936.pnghttp://maxupload.net/images/99027902115904236264.pnghttp://maxupload.net/images/21864643723317666958.pnghttp://maxupload.net/images/10915865178377974582.pnghttp://maxupload.net/images/82927415936462674622.pnghttp://maxupload.net/images/50327483007160319791.pngcreditsfilefactoryonlinewatchttp://www.videozer.com/flash/hwstg.swfhttp://www.fileserve.com/file/zwnnbaj/.2011.1cd.dvdrip.avi.001http://www.filesonic.com/file/1083508024/.2011.1cd.dvdrip
 Row 8 http://www.filesonic.com/file/1083380701/.2011.1cd.dvdrip.avifilesonichttp://www.fileserve.com/file/yjjtjtb/.2011.1cd.dvdrip.avihttp://i.imgur.com/xfvsn.jpghttp://www.duckload.com/download/6110631/.2011.1cd.dvdrip.avihttp://videobb.com/f/dedxm7fgqdxo.swfhttp://maxupload.net/images/44385022067938883936.pnghttp://maxupload.net/images/99027902115904236264.pnghttp://maxupload.net/images/21864643723317666958.pnghttp://maxupload.net/images/10915865178377974582.pnghttp://maxupload.net/images/82927415936462674622.pnghttp://maxupload.net/images/50327483007160319791.pngcreditsfilefactoryonlinewatchttp://www.videozer.com/flash/hwstg.swfhttp://www.fileserve.com/file/zwnnbaj/.2011.1cd.dvdrip.avi.001http://www.filesonic.com/file/1083508024/.2011.1cd.dvdrip
 Row 9 http://www.duckload.com/download/6110631/.2011.1cd.dvdrip.avihttp://videobb.com/f/dedxm7fgqdxo.swfhttp://maxupload.net/images/44385022067938883936.pnghttp://maxupload.net/images/99027902115904236264.pnghttp://maxupload.net/images/21864643723317666958.pnghttp://maxupload.net/images/10915865178377974582.pnghttp://maxupload.net/images/82927415936462674622.pnghttp://maxupload.net/images/50327483007160319791.pngcreditsfilefactoryonlinewatchhttp://www.videozer.com/flash/hwstg.swfhttp://www.fileserve.com/file/zwnnbaj/.2011.1cd.dvdrip.avi.001http://www.filesonic.com/file/1083508024/.2011.1cd.dvdrip
Row 10 http://videobb.com/f/dedxm7fgqdxo.swfhttp://maxupload.net/images/44385022067938883936.pnghttp://maxupload.net/images/99027902115904236264.pnghttp://maxupload.net/images/21864643723317666958.pnghttp://maxupload.net/images/10915865178377974582.pnghttp://maxupload.net/images/82927415936462674622.pnghttp://maxupload.net/images/50327483007160319791.pngcreditsfilefactoryonlinewatchhttp://www.videozer.com/flash/hwstg.swfhttp://www.fileserve.com/file/zwnnbaj/.2011.1cd.dvdrip.avi.001http://www.filesonic.com/file/1083508024/.2011.1cd.dvdrip
 Row 11 http://www.fileserve.com/file/jjbzkhc/.201
 Row 12 http://www.filesonic.com/file/1083509994/.2011.1cd.dvdrip.avi.004http://www.filesonic.com/file/1083507244/.2011.1cd.dvdrip.avi.002http://www.filesonic.com/file/1083431554/.2011.1cd.dvdrip.avi.001http://www.duckload.com/download/6107780/.2011.1cd.dvdrip.srthttp://i.imgur.com/cwmeh.pnghttp://www.filesonic.com/file/1082926804/.2011.1cd.dvdrip.srthttp://lulzimg.com/i22/e9d2cb.jpghttp://www.filefactory.com/file/cb8d162/n/.2011.1cd.dvdrip.avi.001http://www.filefactory.com/file/cb8c711/n/.2011.1cd.dvdrip.srthttp://www.filefactory.com/file/cb8d039/n/.2011.1cd.dvdrip.avi.004http://www.filefactory.com/file/cb8d032/.2011.1cd.dvdrip.avi.003http://www.filefactory.com/file/cb8d02b/.2011.1cd.dvdrip.avi.002http://www.duckload.com/download/6111198/.2011.1cd.dvdrip.avi.004http://www.duckload.com/download/6111177/.2011.1cd.dvdrip.avi.003http://www.fileserve.com/file/jjbzkhc/.201
 Row 13 http://www.duckload.com/download/6107780/.2011.1cd.dvdrip.srthttp://i.imgur.com/cwmeh.pnghttp://www.filesonic.com/file/1082926804/.2011.1cd.dvdrip.srthttp://lulzimg.com/i22/e9d2cb.jpghttp://www.filefactory.com/file/cb8d162/n/.2011.1cd.dvdrip.avi.001http://www.filefactory.com/file/cb8c711/n/.2011.1cd.dvdrip.srthttp://www.filefactory.com/file/cb8d039/n/.2011.1cd.dvdrip.avi.004http://www.filefactory.com/file/cb8d032/.2011.1cd.dvdrip.avi.003http://www.filefactory.com/file/cb8d02b/.2011.1cd.dvdrip.avi.002http://www.duckload.com/download/6111198/.2011.1cd.dvdrip.avi.004http://www.duckload.com/download/6111177/.2011.1cd.dvdrip.avi.003http://www.fileserve.com/file/jjbzkhc/.201
 Row 14 http://www.filefactory.com/file/cb8d162/n/.2011.1cd.dvdrip.avi.001http://www.filefactory.com/file/cb8c711/n/.2011.1cd.dvdrip.srthttp://www.filefactory.com/file/cb8d039/n/.2011.1cd.dvdrip.avi.004http://www.filefactory.com/file/cb8d032/.2011.1cd.dvdrip.avi.003http://www.filefactory.com/file/cb8d02b/.2011.1cd.dvdrip.avi.002http://www.duckload.com/download/6111198/.2011.1cd.dvdrip.avi.004http://www.duckload.com/download/6111177/.2011.1cd.dvdrip.avi.003http://www.fileserve.com/file/jjbzkhc/.201
 Row 15 http://www.fileserve.com/file/wgcnnwu/.2011.1cd.dvdrip.avi.003http://www.fileserve.com/file/quj8aaf/.2011.1cd.dvdrip.avi.004http://www.duckload.com/download/6111037/.2011.1cd.dvdrip.avi.002http://www.duckload.com/download/6111003/.2011.1cd.dvdrip.avi.001http://i.imgur.com/mgabv.png
 Row 16 http://www.duckload.com/download/6111037/.2011.1cd.dvdrip.avi.002http://www.duckload.com/download/6111003/.2011.1cd.dvdrip.avi.001http://i.imgur.com/mgabv.png
 Row 17 http://www.sendspace.com/file/plx178
 Row 18 http://www.mediafire.com/?xufmzdd786albep



The issue is that contents in Rows 3,4,7,8,9,10,12,13,14,15,16 have no delimiters and hence not sure how to separate the cell contents so that they are organized as below




Code: 
Row 1 http://www.uploadstation.com/file/aqyzfzt
 Row 2 http://cramit.in/7ejbvpkg9q0z
Row 3 http://www.uploadstation.com/file/aqyzfzt
http://img607.imageshack.us/img607/8489/31989649.jpg
http://leetleech.org/images/34638303188090584995.gifcreditsscreens
http://img196.imageshack.us/img196/2710/screenft.jpgdvd9teamtntfilesizesource
http://img839.imageshack.us/img839/5092/2011.jpgdvdrip2011widthcodecvideoduration56mn
 Row 4 http://cramit.in/7ejbvpkg9q0z
http://www.uploadstation.com/file/aqyzfzt
http://img607.imageshack.us/img607/8489/31989649.jpg
http://leetleech.org/images/34638303188090584995.gifcreditsscreens
http://img196.imageshack.us/img196/2710/screenft.jpgdvd9teamtntfilesizesource
http://img839.imageshack.us/img839/5092/2011.jpgdvdrip2011widthcodecvideoduration56mn
 Row 5 http://oron.com/duq3g9xvnci6/..2011.1CD.DVDRip.XviD.TeamTNT.avi.html
Row 6 http://www.uploadstation.com/file/EEjYyn6/..2011.1CD.DVDRip.XviD.TeamTNT.avi
 Row 7 http://www.fileserve.com/file/yjjtjtb/.2011.1cd.dvdrip.avi
http://i.imgur.com/xfvsn.jpg
 http://www.duckload.com/download/6110631/.2011.1cd.dvdrip.avi
http://videobb.com/f/dedxm7fgqdxo.swf
http://maxupload.net/images/44385022067938883936.png
http://maxupload.net/images/99027902115904236264.png
http://maxupload.net/images/21864643723317666958.png
http://maxupload.net/images/10915865178377974582.png
http://maxupload.net/images/82927415936462674622.png
http://maxupload.net/images/50327483007160319791.pngcreditsfilefactoryonlinewatch
http://www.videozer.com/flash/hwstg.swf
 http://www.fileserve.com/file/zwnnbaj/.2011.1cd.dvdrip.avi.001
 http://www.filesonic.com/file/1083508024/.2011.1cd.dvdrip
 Row 8 http://www.filesonic.com/file/1083380701/.2011.1cd.dvdrip.avifilesonic
 http://www.fileserve.com/file/yjjtjtb/.2011.1cd.dvdrip.avi
http://i.imgur.com/xfvsn.jpg
 http://www.duckload.com/download/6110631/.2011.1cd.dvdrip.avi
http://videobb.com/f/dedxm7fgqdxo.swf
http://maxupload.net/images/44385022067938883936.png
http://maxupload.net/images/99027902115904236264.png
http://maxupload.net/images/21864643723317666958.png
http://maxupload.net/images/10915865178377974582.png
http://maxupload.net/images/82927415936462674622.png
http://maxupload.net/images/50327483007160319791.pngcreditsfilefactoryonlinewatch
http://www.videozer.com/flash/hwstg.swf
 http://www.fileserve.com/file/zwnnbaj/.2011.1cd.dvdrip.avi.001
 http://www.filesonic.com/file/1083508024/.2011.1cd.dvdrip
 Row 9 http://www.duckload.com/download/6110631/.2011.1cd.dvdrip.avi
http://videobb.com/f/dedxm7fgqdxo.swf
http://maxupload.net/images/44385022067938883936.png
http://maxupload.net/images/99027902115904236264.png
http://maxupload.net/images/21864643723317666958.png
http://maxupload.net/images/10915865178377974582.png
http://maxupload.net/images/82927415936462674622.png
http://maxupload.net/images/50327483007160319791.pngcreditsfilefactoryonlinewatch
http://www.videozer.com/flash/hwstg.swf
 http://www.fileserve.com/file/zwnnbaj/.2011.1cd.dvdrip.avi.001
 http://www.filesonic.com/file/1083508024/.2011.1cd.dvdrip
Row 10 http://videobb.com/f/dedxm7fgqdxo.swf
http://maxupload.net/images/44385022067938883936.png
http://maxupload.net/images/99027902115904236264.png
http://maxupload.net/images/21864643723317666958.png
http://maxupload.net/images/10915865178377974582.png
http://maxupload.net/images/82927415936462674622.png
http://maxupload.net/images/50327483007160319791.pngcreditsfilefactoryonlinewatch
http://www.videozer.com/flash/hwstg.swf
 http://www.fileserve.com/file/zwnnbaj/.2011.1cd.dvdrip.avi.001
 http://www.filesonic.com/file/1083508024/.2011.1cd.dvdrip
 Row 11 http://www.fileserve.com/file/jjbzkhc/.201
 Row 12 http://www.filesonic.com/file/1083509994/.2011.1cd.dvdrip.avi.004
 http://www.filesonic.com/file/1083507244/.2011.1cd.dvdrip.avi.002
 http://www.filesonic.com/file/1083431554/.2011.1cd.dvdrip.avi.001
 http://www.duckload.com/download/6107780/.2011.1cd.dvdrip.srt
http://i.imgur.com/cwmeh.png
 http://www.filesonic.com/file/1082926804/.2011.1cd.dvdrip.srt
http://lulzimg.com/i22/e9d2cb.jpg
 http://www.filefactory.com/file/cb8d162/n/.2011.1cd.dvdrip.avi.001
 http://www.filefactory.com/file/cb8c711/n/.2011.1cd.dvdrip.srt
 http://www.filefactory.com/file/cb8d039/n/.2011.1cd.dvdrip.avi.004
 http://www.filefactory.com/file/cb8d032/.2011.1cd.dvdrip.avi.003
 http://www.filefactory.com/file/cb8d02b/.2011.1cd.dvdrip.avi.002
 http://www.duckload.com/download/6111198/.2011.1cd.dvdrip.avi.004
 http://www.duckload.com/download/6111177/.2011.1cd.dvdrip.avi.003
 http://www.fileserve.com/file/jjbzkhc/.201
 Row 13 http://www.duckload.com/download/6107780/.2011.1cd.dvdrip.srt
http://i.imgur.com/cwmeh.png
 http://www.filesonic.com/file/1082926804/.2011.1cd.dvdrip.srt
http://lulzimg.com/i22/e9d2cb.jpg
 http://www.filefactory.com/file/cb8d162/n/.2011.1cd.dvdrip.avi.001
 http://www.filefactory.com/file/cb8c711/n/.2011.1cd.dvdrip.srt
 http://www.filefactory.com/file/cb8d039/n/.2011.1cd.dvdrip.avi.004
 http://www.filefactory.com/file/cb8d032/.2011.1cd.dvdrip.avi.003
 http://www.filefactory.com/file/cb8d02b/.2011.1cd.dvdrip.avi.002
 http://www.duckload.com/download/6111198/.2011.1cd.dvdrip.avi.004
 http://www.duckload.com/download/6111177/.2011.1cd.dvdrip.avi.003
 http://www.fileserve.com/file/jjbzkhc/.201
 Row 14 http://www.filefactory.com/file/cb8d162/n/.2011.1cd.dvdrip.avi.001
 http://www.filefactory.com/file/cb8c711/n/.2011.1cd.dvdrip.srt
 http://www.filefactory.com/file/cb8d039/n/.2011.1cd.dvdrip.avi.004
 http://www.filefactory.com/file/cb8d032/.2011.1cd.dvdrip.avi.003
 http://www.filefactory.com/file/cb8d02b/.2011.1cd.dvdrip.avi.002
 http://www.duckload.com/download/6111198/.2011.1cd.dvdrip.avi.004
 http://www.duckload.com/download/6111177/.2011.1cd.dvdrip.avi.003
 http://www.fileserve.com/file/jjbzkhc/.201
 Row 15 http://www.fileserve.com/file/wgcnnwu/.2011.1cd.dvdrip.avi.003
 http://www.fileserve.com/file/quj8aaf/.2011.1cd.dvdrip.avi.004
 http://www.duckload.com/download/6111037/.2011.1cd.dvdrip.avi.002
 http://www.duckload.com/download/6111003/.2011.1cd.dvdrip.avi.001
http://i.imgur.com/mgabv.png
 Row 16 http://www.duckload.com/download/6111037/.2011.1cd.dvdrip.avi.002
 http://www.duckload.com/download/6111003/.2011.1cd.dvdrip.avi.001
http://i.imgur.com/mgabv.png
 Row 17 http://www.sendspace.com/file/plx178
 Row 18 http://www.mediafire.com/?xufmzdd786albep

Best Regards,
Mahesh
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
You have a delimiter, it's just not a single character delimiter, that delimiter is http://

Try the following code:
Code: 
Sub test()
Dim c As Range
With Range("A:A")
    .Replace "http://", "`"
    .TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="`", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
End With
For Each c In ActiveSheet.UsedRange
    If c <> "" Then c = "http://" & c
Next
Cells.Columns.AutoFit
End Sub
 
Upvote 0
Greetings!

Tried the code.. it is splitting the cell contents and writing each in separate columns.. this is fine.. could the same be written in rows below..pls check.

but have run into a fresh issue..

some cell contents have comma delimiter too.. which i did not observe earlier.. so when the code is executed.. it works only on cells which do not have comma delimiter and abandons cells with comma delimited contents..

Best Regards,
Mahesh
 
Upvote 0
Greetings!

solved the problem by replacing all comma's with blank.. now the code works flawlessly..with that set of data.

But, when tested with some real data which cannot be posted on any forum.. as they are copyrighted download links, the code processes only the first six row data, and the remaining rows are added with an additional http:// at the start of cell data.

Don't know what the issue could be..

Kindly let know any alternate method whereby, i could send the data for debugging.

Best Regards,
Mahesh
 
Upvote 0
You may not be able to post the specific links, but you can post data in the same form and explain what you're trying to do.
 
Upvote 0
Greetings!

Finally found out the issue.. it was about https:// .

when the code encountered cells with those contents, it just stopped.

Not an issue at all, will take care of it..

Wish to extend my Thanks to you all in helping me solve this issue.

Best Regards,
Mahesh
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Match sheet name for cell with sheet name and repeat filling blank cells
2
Replies
13
Views
756
Mario_R
M
P
  • Question Question
Excel VBA Compare two array and create result array
Replies
2
Views
593
Perter Parker
P
A
  • Question Question
Need to concatenate with last rows
Replies
0
Views
898
aravindh8686
A
P
  • Question Question
Special Characters and Blank Cells
Replies
5
Views
2K
Fishboy
Fishboy
B
  • Question Question
Freelancer Needed for Excel Macro / Button [Paid]
Replies
5
Views
548
RoryA
RoryA

Forum statistics

Threads
1,224,513
Messages
6,179,214
Members
452,895
Latest member
BILLING GUY

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