Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

convert web addresses 2

Status
Not open for further replies.

RodP

Programmer
Jan 9, 2001
109
GB
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

 
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
 
Hi xlbo,

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

Thanks

RodP
 
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:

Code:
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
 
Hi Vroscioli,

Cheers for that insight. Will amend my code accordingly.

Many thanks

RodP
 
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
 
A starting point:
MsgBox "%26=" & Chr(2*16+6) & ", %27=" & Chr(2*16+7)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top