Advanced Transpose and/or grouping help

[JustinW1]

I need help, I'm on a MAC and don't know how to use VBA's. Previously I would have used MS Access, but.... Mac!

Here is what my data looks like:

PDP URL	IMAGE URL	IMAGE ALT TEXT
abc.com/page1.html	abc.com/images/banana.jpg	Banana
abc.com/page1.html	abc.com/images/apple.jpg	Apple
abc.com/page1.html	abc.com/images/pair.jpg	Pair
abc.com/page2.html	abc.com/images/orange.jpg	Orange
abc.com/page3.html	abc.com/images/pineapple.jpg	Pineapple
abc.com/page3.html	abc.com/images/melon.jpg	Melon

What I'm trying to achieve is:

PDP URL	IMAGE 1 URL	IMAGE 2 ALT TEXT	IMAGE 2 URL	IMAGE 2 ALT TEXT	IMAGE 3 URL	IMAGE 3 ALT TEXT
abc.com/page1.html	abc.com/images/banana.jpg	Banana	abc.com/images/apple.jpg	Apple	abc.com/images/pair.jpg	Pair
abc.com/page2.html	abc.com/images/orange.jpg	Orange
abc.com/page3.html	abc.com/images/pineapple.jpg	Pineapple	abc.com/images/melon.jpg	Melon

Hopefully you'll see what I'm trying to achieve.

Apologies if this is straightforward, I'm pretty good at basic spreadsheets but this has stumped me, and my Google searches.

Next fruit salad is on me

 

[bobsan42]

try this UDF:

Public Function abc(Target As Range, Optional separator As String = "/") As String
    abc = ""
    Dim str As String, i As Long
    On Error Resume Next
    str = Target.Text
    If Len(str) = 0 Then Exit Function
    i = InStrRev(str, separator, , 1)
    str = Right(str, Len(str) - i)
    i = InStrRev(str, ".", , 1)
    str = Left(str, i - 1)
    abc = WorksheetFunction.Proper(str)
End Function

 

[JustinW1]

try this UDF:

Public Function abc(Target As Range, Optional separator As String = "/") As String
    abc = ""
    Dim str As String, i As Long
    On Error Resume Next
    str = Target.Text
    If Len(str) = 0 Then Exit Function
    i = InStrRev(str, separator, , 1)
    str = Right(str, Len(str) - i)
    i = InStrRev(str, ".", , 1)
    str = Left(str, i - 1)
    abc = WorksheetFunction.Proper(str)
End Function

Thanks for the helo bobsan42 but I can't get this to work for me. When I run the macro, nothing happens.

 

[bobsan42]

this is not a macro to run - it is a Function.
Place the code in a code module in your workbook, then use it as formula in a cell: = abc(A1)