Page 1 of 2 12 LastLast
Results 1 to 10 of 11

vba to convert text string

This is a discussion on vba to convert text string within the Excel Questions forums, part of the Question Forums category; I'm looking to convert a text string in a cell, letter by letter. I can do it with vlookup by ...

  1. #1
    Board Regular dnickelson's Avatar
    Join Date
    Oct 2003
    Posts
    118

    Default vba to convert text string

    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"

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: vba to convert text string

    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

  3. #3
    Board Regular dnickelson's Avatar
    Join Date
    Oct 2003
    Posts
    118

    Default Re: vba to convert text string

    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

  4. #4
    Board Regular
    Join Date
    May 2002
    Location
    CT
    Posts
    2,915

    Default Re: vba to convert text string

    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

  5. #5
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,113

    Default Re: vba to convert text string

    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

  6. #6
    Board Regular dnickelson's Avatar
    Join Date
    Oct 2003
    Posts
    118

    Default Re: vba to convert text string

    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

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default Re: vba to convert text string

    Quote Originally Posted by dnickelson
    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:

    Sub hexTime()
    Dim b() As Byte, myStr As String, myArr As Variant, i As Long
    For Each cl In [a1:a50]
    ****With WorksheetFunction
    ********myArr = .Transpose(.Transpose(cl.Resize(, 5).Value))
    ****End With
    ****myStr = myStr & Join(myArr, vbNullString)
    Next
    b = StrConv(myStr, vbFromUnicode): Let myStr = Empty
    For i = LBound(b) To UBound(b)
    ****myStr = myStr & hex(b(i))
    Next
    Debug.Print myStr
    End Sub


    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.

  8. #8
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,922

    Default Re: vba to convert text string

    You might also want to visit the Excel | VBA page of my web site and follow the link to 'Simple Encoding'
    http://www.tushar-mehta.com/excel/vb...e_encoding.htm

    Quote Originally Posted by dnickelson
    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
    {snip}

  9. #9
    Board Regular dnickelson's Avatar
    Join Date
    Oct 2003
    Posts
    118

    Default Re: vba to convert text string

    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.

  10. #10
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    10,922

    Default Re: vba to convert text string

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

    Quote Originally Posted by NateO
    {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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com