Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-25-2018, 02:48 PM
Shoxyk Shoxyk is offline Convert Text to HEX Mac OS X Convert Text to HEX Office 2016 for Mac
Novice
Convert Text to HEX
 
Join Date: Jul 2018
Posts: 1
Shoxyk is on a distinguished road
Default 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!
Reply With Quote
  #2  
Old 07-26-2018, 07:51 AM
NoSparks NoSparks is offline Convert Text to HEX Windows 7 64bit Convert Text to HEX Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #3  
Old 07-27-2018, 02:50 AM
p45cal's Avatar
p45cal p45cal is offline Convert Text to HEX Windows 10 Convert Text to HEX Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

A formula too:
=CONCAT(DEC2HEX(CODE(MID(A1,{1,2,3,4,5,6,7,8},1))) )
Reply With Quote
  #4  
Old 07-28-2018, 07:46 AM
NoSparks NoSparks is offline Convert Text to HEX Windows 7 64bit Convert Text to HEX Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

@ 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.
Reply With Quote
  #5  
Old 07-28-2018, 03:20 PM
p45cal's Avatar
p45cal p45cal is offline Convert Text to HEX Windows 10 Convert Text to HEX Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.

Last edited by p45cal; 07-29-2018 at 05:25 AM.
Reply With Quote
  #6  
Old 07-29-2018, 05:09 AM
NoSparks NoSparks is offline Convert Text to HEX Windows 7 64bit Convert Text to HEX Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Thanks p45cal.
I'm just go good with formulas.
Reply With Quote
Reply

Tags
covert, hex, text

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert Text to HEX 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 HEX 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 Text to HEX 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft