Convert text to hex

S

stephen1

New Member
Joined
Sep 4, 2015
Messages
4
How do I convert text to hex? A1 has "google.com". I want to convert those text characters to hexadecimal in B1.
I use a Mac, Excel 2011.
Thank you.
Stephen
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Welcome to the forum.

You can only convert numbers to hex, not text (unless that text would be a representation of a number in some format).

In Excel 2013 you can use the DECIMAL function to convert "googlecom" (without .) to a number (e.g. if you use a numeric format up to 36, so 0-9 A-Z) and the BASE function to convert the outcome to hexadecimal, returning 2ACFBE9E2CB6, but I don't think this does make much sense at all.

Nevertheless, this would be, only in Excel 2013:
Code: 
=BASE(DECIMAL(A1,36),16)
 
Upvote 0
stephen1 said:
How do I convert text to hex? A1 has "google.com". I want to convert those text characters to hexadecimal in B1.
I use a Mac, Excel 2011.
Click to expand...
Are you looking to convert each letter to its ASCII code where the code values are displayed using Hex digits (if so, what exactly should the output look like)? If not, exactly what Hex value do you want?
 
Upvote 0
I know this is not exactly what you are looking for but it is close.
=TRANSPOSE(DEC2HEX(CODE(MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1))))
"array enter it by pressing CTL+SHIFT+ENTER"
this will output each characters hex values in an array. You will only see the first value but all the values are there in the array. Maybe a user defined function to combine into a string.
 
Upvote 0
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][/tr][tr][td]
1​
[/td][td]google.com[/td][td][/td][/tr]
[tr][td]
2​
[/td][td]676F6F676C652E636F6D[/td][td]A2: =Str2Asc(A1)[/td][/tr]
[tr][td]
3​
[/td][td]google.com[/td][td]A3: =Asc2Str(A2)[/td][/tr]
[/table]

Code: 
Function Str2Asc(ByVal sInp As String) As String
    Dim i           As Long

    For i = 1 To Len(sInp)
        Str2Asc = Str2Asc & Right$("0" & Hex$(Asc(Mid$(sInp, i, 1))), 2)
    Next i
End Function

Function Asc2Str(ByVal sInp As String) As String
    Dim i           As Long

    For i = 1 To Len(sInp) Step 2
        Asc2Str = Asc2Str & Chr$("&H" & Mid$(sInp, i, 2))
    Next i
End Function
 
Upvote 0
shg said:
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]
A​
[/td][td]
B​
[/td][/tr][tr][td]
1​
[/td][td]google.com[/td][td][/td][/tr]
[tr][td]
2​
[/td][td]676F6F676C652E636F6D[/td][td]A2: =Str2Asc(A1)[/td][/tr]
[tr][td]
3​
[/td][td]google.com[/td][td]A3: =Asc2Str(A2)[/td][/tr]
[/table]

Code: 
Function Str2Asc(ByVal sInp As String) As String
    Dim i           As Long

    For i = 1 To Len(sInp)
        Str2Asc = Str2Asc & Right$("0" & Hex$(Asc(Mid$(sInp, i, 1))), 2)
    Next i
End Function

Function Asc2Str(ByVal sInp As String) As String
    Dim i           As Long

    For i = 1 To Len(sInp) Step 2
        Asc2Str = Asc2Str & Chr$("&H" & Mid$(sInp, i, 2))
    Next i
End Function
Click to expand...
It is a weekend and I am bored :laugh:, so I decided to write your two functions as one-liners :eek:...
Code: 
[table="width: 500"]
[tr]
	[td]Function Str2Asc(ByVal sInp As String) As String
  Str2Asc = Join(Evaluate("TRANSPOSE(IF(LEN(""" & sInp & """),DEC2HEX(CODE(MID(""" & sInp & """,ROW(1:" & Len(sInp) & "),1))),""""))"), "")
End Function

Function Asc2Str(ByVal sInp As String) As String
  Asc2Str = Join(Evaluate("TRANSPOSE(IF(LEN(""" & sInp & """),CHAR(HEX2DEC(MID(""" & sInp & """,2*ROW(1:" & Len(sInp) / 2 & ")-1,2))),""""))"), "")
End Function[/td]
[/tr]
[/table]
 
Last edited:
Upvote 0
stephen1 said:
How do I convert text to hex?
Click to expand...

Hi Stephen
Welcome to the board

What kind of text? Excel strings have Unicode text characters, that allow you for ex. to include characters used in any language.
The solutions posted assume you have ascii text. Is that the case, do you just need the ascii's (more or less the 26 letters+10digits+punctuation) used in the English language? Or do you want the code for any characters?
 
Upvote 0
MarcelBeug said:
Welcome to the forum.

You can only convert numbers to hex, not text (unless that text would be a representation of a number in some format).

In Excel 2013 you can use the DECIMAL function to convert "googlecom" (without .) to a number (e.g. if you use a numeric format up to 36, so 0-9 A-Z) and the BASE function to convert the outcome to hexadecimal, returning 2ACFBE9E2CB6, but I don't think this does make much sense at all.

Nevertheless, this would be, only in Excel 2013:
Code: 
=BASE(DECIMAL(A1,36),16)
Click to expand...

Thank you. I got a #NAME? error. I will look at other ideas.
 
Upvote 0
That free time will be convenient to have when you run your one-liner versions :)

Range​
Formula​
Cells​
Iterations​
TotalCalcs​
Time​
[td]
Time/Calc​
[/td][td]
RelSpeed​
[/td]
[tr][td]C2:C1001[/td][td] =Str2Asc(A2)[/td][td]
1,000​
[/td][td]
256​
[/td][td]
256,000​
[/td][td]
3.359​
[/td][td]
0.000 013 123​
[/td][td]
9.7​
[/td][/tr]
[tr][td]F2:F1001[/td][td] =Str2AscRR(A2)[/td][td]
1,000​
[/td][td]
16​
[/td][td]
16,000​
[/td][td]
2.039​
[/td][td]
0.000 127 441​
[/td][td]
1.0​
[/td][/tr]
[tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
Range​
[/td][td]
Formula​
[/td][td]
Cells​
[/td][td]
Iterations​
[/td][td]
TotalCalcs​
[/td][td]
Time​
[/td][td]
Time/Calc​
[/td][td]
RelSpeed​
[/td][/tr]
[tr][td]D2:D1001[/td][td] =Asc2Str(C2)[/td][td]
1,000​
[/td][td]
256​
[/td][td]
256,000​
[/td][td]
3.066​
[/td][td]
0.000 011 978​
[/td][td]
10.5​
[/td][/tr]
[tr][td]G2:G1001[/td][td] =Asc2StrRR(F2)[/td][td]
1,000​
[/td][td]
16​
[/td][td]
16,000​
[/td][td]
2.004​
[/td][td]
0.000 125 244​
[/td][td]
1.0​
[/td][/tr]
 
Upvote 0
shg said:
That free time will be convenient to have when you run your one-liner versions :)

Range​
Formula​
Cells​
Iterations​
TotalCalcs​
Time​
[td]
Time/Calc​
[/td][td]
RelSpeed​
[/td]
[tr][td]C2:C1001[/td][td] =Str2Asc(A2)[/td][td]
1,000​
[/td][td]
256​
[/td][td]
256,000​
[/td][td]
3.359​
[/td][td]
0.000 013 123​
[/td][td]
9.7​
[/td][/tr]
[tr][td]F2:F1001[/td][td] =Str2AscRR(A2)[/td][td]
1,000​
[/td][td]
16​
[/td][td]
16,000​
[/td][td]
2.039​
[/td][td]
0.000 127 441​
[/td][td]
1.0​
[/td][/tr]
[tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]
Range​
[/td][td]
Formula​
[/td][td]
Cells​
[/td][td]
Iterations​
[/td][td]
TotalCalcs​
[/td][td]
Time​
[/td][td]
Time/Calc​
[/td][td]
RelSpeed​
[/td][/tr]
[tr][td]D2:D1001[/td][td] =Asc2Str(C2)[/td][td]
1,000​
[/td][td]
256​
[/td][td]
256,000​
[/td][td]
3.066​
[/td][td]
0.000 011 978​
[/td][td]
10.5​
[/td][/tr]
[tr][td]G2:G1001[/td][td] =Asc2StrRR(F2)[/td][td]
1,000​
[/td][td]
16​
[/td][td]
16,000​
[/td][td]
2.004​
[/td][td]
0.000 125 244​
[/td][td]
1.0​
[/td][/tr]
Click to expand...

I am not sure I understand your tables (since I don't have a loop, what are the iterations?)... are you saying those one-liners were quicker than your code? If so, I actually was not going for that, rather, I was just "having fun".
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Protect Workbook with strong encryption password.
Replies
3
Views
209
6StringJazzer
6StringJazzer
K
  • Question Question
Top of text gets cut off in newer Excel versions when printing / converting to PDF
Replies
7
Views
105
kjhung
K
WESTERNWALL
  • Solved
Convert text date to date in excel for Mac
2
Replies
14
Views
552
Alex Blakenburg
A
M
  • Question Question
Convert text
Replies
2
Views
301
maclachlan19
M
W
  • Question Question
Index/Match Duplicate Values With No Helper Cells
Replies
3
Views
97
Domenic
D

Forum statistics

Threads
1,222,579
Messages
6,166,876
Members
452,080
Latest member
Akin Himself

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top