Convert Text to HEX

Shoxyk · #1 07-25-2018, 02:48 PM

Hello,

I need help. I have a column of positions (01-01-01 ... 99-99-99) and I need that every cell of that column / every position convert to hex.
For example: 01-01-01 would be 30312d30312d3031

Thanks for your help!

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

p45cal · #3 07-27-2018, 02:50 AM

A formula too:
=CONCAT(DEC2HEX(CODE(MID(A1,{1,2,3,4,5,6,7,8},1))) )

NoSparks · #4 07-28-2018, 07:46 AM

@ p45cal
that formula looks neat, how do I get it to work?
Having Excel 2010, have changed to
=CONCATENATE(DEC2HEX(CODE(MID(A2,{1,2,3,4,5,6,7,8} ,1))) )
but only get conversion of the first character.

p45cal · #5 07-28-2018, 03:20 PM

Ahh… shame.

I see the OP has Excel2016 for the Mac and that does have the CONCAT function.

I can't think of an array formula to do this off the top of my head, but the following works:

Code:

=DEC2HEX(CODE(MID(A1,1,1)))&DEC2HEX(CODE(MID($A1,2,1)))&DEC2HEX(CODE(MID($A1,3,1)))&DEC2HEX(CODE(MID($A1,4,1)))&DEC2HEX(CODE(MID($A1,5,1)))&DEC2HEX(CODE(MID($A1,6,1)))&DEC2HEX(CODE(MID($A1,7,1)))&DEC2HEX(CODE(MID($A1,8,1)))

but you lose all simplicity and speed.

If you:
=SUMPRODUCT(DEC2HEX(CODE(MID(A2,{1,2,3,4,5,6,7,8}, 1))))
you can see all the results in the SUMPRODUCT dialogue box. Perhaps you can think of a way.

NoSparks · #6 07-29-2018, 05:09 AM

Thanks p45cal.
I'm just go good with formulas.

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