convert web addresses 2

[RodP]

Hi Everyone,

I have a long list of hyperlinks for which i have converted into text to get hold of the filenames (for example). However, like normal internet addresses, you can't use certain characters and so they are replaced with other characters.

eg:
What%27s on Weetabix.mpg
should be: What's on Weetabix.mpg

Weetabix - Fruit %26 Nut.mpg
should be: Weetabix - Fruit & Nut.mpg

Is there a function already installed in excel that will convert the string back?

Many thanks in advance

RodP

 

[xlbo]

no inbuilt function. You would need to use a lookup table and do a global find and replace on each item in the lookup table

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[RodP]

Hi xlbo,

Thanks for this. Do you know where I can get a full listing of what %xx values mean what?

Thanks

RodP

 

[VRoscioli]

While there isn't really a built-in function, a look-up table is not necessary either. The illegal characters are always replaced by %followed by their ASCII code (in hex). The following function should replace them:

Public Function FixWebNames(inputName As String) As String

Dim x As Integer

x = 1
Do Until x > Len(inputName) - 2
    If Mid$(inputName, x, 1) = "%" Then inputName = Left$(inputName, x - 1) & Chr$(Val("&H" & Mid$(inputName, x + 1, 2))) & Right$(inputName, Len(inputName) - x - 2)
    x = x + 1
Loop

FixWebNames = inputName

End Function

-V

 

[RodP]

Hi Vroscioli,

Cheers for that insight. Will amend my code accordingly.

Many thanks

RodP

 

[xlbo]

Indeed - I didn't know that - have a star for that useful tidbit!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

A starting point:
MsgBox "%26=" & Chr(2*16+6) & ", %27=" & Chr(2*16+7)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886