#1
|
|||
|
|||
Convert Text to HEX
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! |
#2
|
|||
|
|||
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 |
#3
|
||||
|
||||
A formula too:
=CONCAT(DEC2HEX(CODE(MID(A1,{1,2,3,4,5,6,7,8},1))) ) |
#4
|
|||
|
|||
@ 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. |
#5
|
||||
|
||||
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))) 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. Last edited by p45cal; 07-29-2018 at 05:25 AM. |
#6
|
|||
|
|||
Thanks p45cal.
I'm just go good with formulas. |
Tags |
covert, hex, text |
Thread Tools | |
Display Modes | |
|
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 |