character spacing

[thenos09]

I would just like to know how to format a cell to make a space every 2 characters

ie. cell A1 = 1234567890

I want to the cell to display 12 34 56 78 90

the data in cell A1 will vary but I always want a space after every 2nd character

 

[thenos09]

I did that.

So in A1 i have 'Hello'

Then in A2 i have '48656C6C6F'

Then in A3 i have '=InsertSpace(A2)' and in the cell it says '#VALUE!'

 

[Peter_SSs]

Why not do it all at once? I assume that your asc2hex function is something like below but without the blue bits. If you add the blue bits, doesn't that give you the result you want? If you want what you currently have in A2 as well, then just rename the function below as, say, asc2hexspace and use in A3:
=asc2hexspace(A1)

Function asc2hex(str As String) As String
    Dim I As Long
    
    For I = 1 To Len(str)
        asc2hex = asc2hex & Hex(Asc(Mid(str, I, 1))) & " "
    Next I
    asc2hex = RTrim(asc2hex)
End Function

 

[thenos09]

Thank you Peter. Works exactly how i wanted.

Appreciate your time and effort!!!!!

 

[Rick Rothstein]

Here is a UDF you can use to do all the work (convert the text to hex values separated by spaces)...

Function Asc2Hex(S As String) As String
  Dim X As Long, B() As Byte, H() As String
  B = S
  ReDim H(LBound(B) To UBound(B))
  For X = LBound(B) To UBound(B)
    If X Mod 2 Then
      H(X) = " "
    Else
      H(X) = Hex(B(X))
    End If
  Next
  Asc2Hex = Trim(Join(H, ""))
End Function

So, if you text string "Hello my name is Brett" is in A1, then this formula...

=Asc2Hex(A1)

will output this...

48 65 6C 6C 6F 20 6D 79 20 6E 61 6D 65 20 69 73 20 42 72 65 74 74