Convert Characters Based On Custom Map

[encodeme]

Hi,
I'm looking for a macro to convert each character in a string into another character based on a custom map. I realize I could use VLOOKUP but then I'd need to somehow break out each character individually, return each value, then concatenate everything back together. It's not ideal. Any help would be much appreciated!

Thanks!

[StevenM]

Can you give us more details?

[encodeme]

Sure. Say if a cell contain the value "welcome". In the adjacent cell to the right I'd like the translated/encoded value "SXBO-MX". Each character would be converted based on the attached map.

w=S
e=X
l=B
c=O
o=-
m=M
e=X

m_map.xlsx

Thanks!

[StevenM]

See if this works for you.
For what it is worth, if you mapped your character set to an ASCII order,
(see: http://www.asciitable.com/) for values 32 to 122,
then you would only need one Const string, instead of two.

Please Login or Register  to view this content.

[encodeme]

Thanks Steven, this works great! After assigning sText to reference A1 and returning sResult in B1, how can I repeat it until it reaches the end of the list or the first blank?

Please Login or Register  to view this content.

Thanks!

[StevenM]

Version 2 works like a UDF and can be used in any column.

Please Login or Register  to view this content.

Version 3 takes text in column A and places the code in column B.

Please Login or Register  to view this content.

[encodeme]

Both are great, thanks again Steven!