Convert Text to HEX

NoSparks · #2 07-26-2018, 07:51 AM

Assumes data in column A starting in row 2.
Writes result to adjacent column B cell.
Probably not the best way. It's slow.
For all 989,899 possibilities takes 36 seconds on my computer.

Code:

Sub Convert_to_Hex()
Dim rng As Range, cel As Range
Dim i As Long, str As String, tmp As String

Application.ScreenUpdating = False

Set rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
For Each cel In rng
    str = cel.Value
    
    For i = 1 To Len(str)
        Select Case Asc(Mid(str, i, 1))
            Case Is = 45    'hyphen
                tmp = tmp & "2d"
            Case Is = 48    'zero
                tmp = tmp & "30"
            Case Is = 49    'one
                tmp = tmp & "31"
            Case Is = 50    'two
                tmp = tmp & "32"
            Case Is = 51    'three
                tmp = tmp & "33"
            Case Is = 52    'four
                tmp = tmp & "34"
            Case Is = 53    'five
                tmp = tmp & "35"
            Case Is = 54    'six
                tmp = tmp & "36"
            Case Is = 55    'seven
                tmp = tmp & "37"
            Case Is = 56    'eight
                tmp = tmp & "38"
            Case Is = 57    'nine
                tmp = tmp & "39"
        End Select
    Next i
    
    cel.Offset(, 1).Value = tmp
    tmp = ""
Next cel

Application.ScreenUpdating = True

End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I need to convert shaded text into highlighted text on about 80 different long documents. VBA macro?	AustinBrister	Word VBA	8	05-28-2015 02:42 PM
convert text to tables	oraenthu	Word	8	03-17-2015 10:22 AM
Convert Text To Table: Separate text at (Other)?	tinfanide	Word VBA	2	01-12-2015 05:26 AM
Convert image-text hybrids into plain text	morlack	Excel	4	12-03-2014 05:29 PM
My plain text post got converted to rich text in a reply, how to convert it back?	david.karr	Outlook	0	01-05-2012 09:46 AM