vba to convert text string

[dnickelson]

I'm looking to convert a text string in a cell, letter by letter.
I can do it with vlookup by grabbing each letter into a cell, then running vlookup with the conversion table, but the text can be up to 50 characters long and I'm looking to do this with 40 or so rows with 4 - 5 column.
I can't fit 50 vlookup formulas into 1 formula because of length, and using a cell per letter, then combining the results in a single cell is kind of a logistics nightmare.

I think a vba loop would work pretty good, but am unsure how to write it, let alone link it to a lookup table. Any examples available?

the text would be similar to a password and the conversion would be to hex, thus the text "nickelson" would be returned as "4E49434B454C534F4E"

 

[NateO]

Ah, I see what you're saying, I took your post literally, so your description was backwards:

nickelson: 6E69636B656C736F6E

NICKELSON: 4E49434B454C534F4E

That's because of VLOOKUP -- a case-insensitive function.

My routine does not invoke the vlookup() algorithm. While browsing Tushar's site will not hurt, I've done so myself, my routine seems to work just fine, handling case differences and numbers.

If you wanted it to look at everything in Upper Case, the tweak is very straight-forward:

b = StrConv(UCase(myStr), vbFromUnicode): Let myStr = Empty

It returns a single string, but you could easy stack a collection or something and slam the values back into cells.