vba to convert text string

dnickelson

Board Regular
Joined
Oct 30, 2003
Messages
118
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"
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
can you tell us where the lookup is and what comprises it?

eg

Lookup on Sheet2 Range A:B
A contains letter
B contains conversion
 
Upvote 0
Yes, actually I'm getting pretty close, I'll post my code below
basically I have sheet1 with a range of cells with usernames, passwords, links, etc, from, say, A1 to D5
I can put the conversion table wherever, probably different sheet, but for now on sheet1 from F10:G90
I want Sheet2 A1:D5 to contain the same cells as Sheet1, but each character in each cell converted according to the conversion table

I think I have the conversion in this formula

Sub convert()
Dim target As Range
Dim text As Variant
Dim letter As Variant
Dim i As Long
Dim lookRange As Range
Dim res As Variant
i = 1
Set target = Range("E5")
text = target.Value
For i = 1 To 30
Set lookRange = Worksheets("Sheet1").Range("B1:C80")
letter = Mid(text, i, 1)
res = Application.VLookup(letter, lookRange, 2, False)
If IsError(res) Then
res = ""
Else
Range("D1").Value = Range("D1").Value & res
End If
Next
End Sub

maybe a little extra here too, but I haven't come up with the loop for looking each cell one at a time and converting. Having trouble getting my head around skipping form cell to cell. Any help would be appreciated
(also, the above formula doesn't follow the parameters I set for where the data is\goes yet, but it does convert a text string
 
Upvote 0
How about making this a function instead. Then just apply the function cell by cell. This is untested but I converted your macro to a function.

Function convert(target As String) As Variant
Dim text As Variant
Dim letter As Variant
Dim i As Long
Dim lookRange As Range
Dim res As Variant
i = 1
text = target.Value
For i = 1 To 30
Set lookRange = Worksheets("Sheet1").Range("B1:C80")
letter = Mid(text, i, 1)
res = Application.VLookup(letter, lookRange, 2, False)
If IsError(res) Then
res = ""
Else
convert = convert & res
End If
Next
End Function
 
Upvote 0
Just put a double loop in there by column and row so range A1:D5 gets processed - presumed Sheet1 held original data and Sheet2 was where converted data was to go - is that what you wanted? Again not tested...so may have missed some blatant point!

Code:
Sub convert()
Dim target As Range
Dim text As Variant
Dim letter As Variant
Dim i As Long
Dim lookRange As Range
Dim res As Variant

Sheets("sheet1").Select

c = 1
Do Until c > 4
    r = 1
    Do Until r > 5
        i = 1
        Set target = Cells(r, c)
        text = target.Value
        For i = 1 To 30
            Set lookRange = Worksheets("Sheet1").Range("B1:C80")
            letter = Mid(text, i, 1)
            res = Application.VLookup(letter, lookRange, 2, False)
            If IsError(res) Then
                res = ""
            Else
                Sheets("sheet2").Cells(r,c) = Sheets("Sheet1").Cells(r,c) & res
            End If
        Next i
    r = r + 1
    Loop
c = c + 1
Loop

End Sub
 
Upvote 0
works pretty slick as a function,
here's a version that converts the sheet at once, finall figured it out, but it wouldn't do numbers. May work now, the funciton had the same issue and that's resolved. (conversion table was saved as text, but had to select the numbers, and click ignore error on value stored as text)

Sub convert()

Dim text As Variant
Dim letter As Variant
Dim i As Long
Dim lookRange As Range
Dim res As Variant
Dim a As Long
Dim b As Long

Set lookRange = Worksheets("Sheet2").Range("A1:B80")

For a = 1 To 6
For b = 1 To 50
text = Sheets("pas").Cells(b, a).Value
If text = "" Then
Sheets("Sheet1").Cells(b, a).Value = ""
Else
Sheets("Sheet1").Cells(b, a).Value = "a"
End If

For i = 1 To 30

letter = Mid(text, i, 1)
res = Application.VLookup(letter, lookRange, 2, False)
If IsError(res) Then
res = letter
Else
Sheets("Sheet1").Cells(b, a).Value = Sheets("Sheet1").Cells(b, a).Value & res
End If
Next
Next
Next

End Sub
 
Upvote 0
dnickelson said:
I'm looking to convert a text string in a cell, letter by letter.
....
the text would be similar to a password and the conversion would be to hex, thus the text "nickelson" would be returned as "4E49434B454C534F4E"
{snip}

The following kind of sort of seemed to work:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> hexTime()
<SPAN style="color:darkblue">Dim</SPAN> b() <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Byte</SPAN>, myStr <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, myArr <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Variant</SPAN>, i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Long</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> cl <SPAN style="color:darkblue">In</SPAN> [a1:a50]
    <SPAN style="color:darkblue">With</SPAN> WorksheetFunction
        myArr = .Transpose(.Transpose(cl.Resize(, 5).Value))
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
    myStr = myStr & Join(myArr, vbNullString)
<SPAN style="color:darkblue">Next</SPAN>
b = StrConv(myStr, vbFromUnicode): <SPAN style="color:darkblue">Let</SPAN> myStr = <SPAN style="color:darkblue">Empty</SPAN>
<SPAN style="color:darkblue">For</SPAN> i = <SPAN style="color:darkblue">LBound</SPAN>(b) <SPAN style="color:darkblue">To</SPAN> <SPAN style="color:darkblue">UBound</SPAN>(b)
    myStr = myStr & hex(b(i))
<SPAN style="color:darkblue">Next</SPAN>
<SPAN style="color:darkblue">Debug</SPAN>.<SPAN style="color:darkblue">Print</SPAN> myStr
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

But, nickelson returned:

6E69636B656C736F6E versus
4E49434B454C534F4E

You can see what you expected to be 4's came back as 6's and a 5 in one instance became a 7.
 
Upvote 0
thanks all, got it working, as far as the 4-6, 5-7 difference, I would assume your code returned each letter as CAPS maybe, since that would be the difference between upper and lower case.

otherwise, both the function and the loop examples worked. I ended up going with the function because it's a little more portable, but thanks to all for helping make VBA make a little more sense.

PS - i didn't get to try out Nate's version yet, but it looks like that might be the shortest path

Thanks again, and if anyone knows any examples of sqlrequest run in vba, I'll be posting a question for you soon. Returning the results has got me lost.
 
Upvote 0
That's because of VLOOKUP -- a case-insensitive function.

NateO said:
{snip}

But, nickelson returned:

6E69636B656C736F6E versus
4E49434B454C534F4E

You can see what you expected to be 4's came back as 6's and a 5 in one instance became a 7.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top