CODEWITHSUNDEEP
excelcsvurlcells
jakeT888
jakeT888

Reputation: 131

How to mass edit the format of cells in a CSV file - Excel 2013

Right now I have a CSV file with thousands of URLs. Each row contains a URL in this format: 5,google.com and this 1750,facebook.com. Each number is a URL count.

Basically I want to change the format to this: http://www.google.com for all URLs so I can import the CSV to a python script and do some crawling on the URLs.

I managed to automatically add http://www. in front of all thousands URLs by custom formatting the cells. But I couldn't get rid of the counting numbers and the , in front of the URLs. Now I'm stuck with this: http://www.1,google.com.

How do I automatically sort these URLs to the desired format as mentioned above?

Upvotes: 1

Views: 1333

Answers (1)

Gordon
Gordon

Reputation: 1165

Try this formula. If your text is in column A then enter this formula in B1 and autofill down.

="http://www." & RIGHT(I16,LEN(A1)-FIND(",",A1,1))

Also, you can make each cell a hyperlink

=HYPERLINK("http://www." & RIGHT(I16,LEN(I16)-FIND(",",A1,1)),"http://www." & RIGHT(I16,LEN(I16)-FIND(",",A1,1)))

Upvotes: 1

Related Questions