I have a csv file. When I open the csv file in Microsoft excel, I have a column.

Each cell in the column has number of paragraphs. each paragraph has many URLs.

I am looking to extract all URLs in each cell of the column and put each URL in separate cell in a new separate column.

All URLs start with http:// and finish with .jpg

How can I do this in Excel or Notepad++

Thank you in advance

  • As I asked in your last question, please edit and provide before and after data. – DavidPostill Feb 21 '17 at 12:31

Here is a code that will split cells with linefeeds into columns. You should be able to tweak it depending on what your paragraphs look like.

Sub SplitCellsAndExtend_New()
'takes cells with inside line feeds and creates new row for each.
'reverses merge into top cell.

'turn off updates to speed up code execution
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Dim strCell As String, lastRow As Long, lRowLoop As Long, j As Long, arSplit
Application.ScreenUpdating = False

Const lColSplit As Long = 4
Const sFirstCell As String = "A1"
Dim sSplitOn As String
sSplitOn = Chr(10)

lastRow = Cells(Rows.Count, lColSplit).End(xlUp).Row

    For lRowLoop = lastRow To 1 Step -1

        arSplit = Split(Cells(lRowLoop, lColSplit), sSplitOn)

        If UBound(arSplit) > 0 Then
            Rows(lRowLoop + 1).Resize(UBound(arSplit) + 1).Insert

            Cells(lRowLoop, lColSplit).Resize(, UBound(arSplit) + 1).Value = arSplit
            Cells(lRowLoop, lColSplit).Resize(, UBound(arSplit) + 1).Copy
            Cells(lRowLoop + 1, lColSplit).PasteSpecial Transpose:=True

            Cells(lRowLoop, 1).Resize(, lColSplit - 1).Copy Cells(lRowLoop + 1, 1).Resize(UBound(arSplit) + 1)

        End If

        Set arSplit = Nothing

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.