2

If my Unicode character contains more than one hex value in an Excel cell, how do I write a function in Excel to convert it into the corresponding character?

For example: Unicode Hex values in Excel cell: 0B15 0B4D 0B37. Character to be formed out of above values: କ୍ଷ (Odia/Indian language)

  • Welcome to Super User! More details are needed. Please tell us what you have researched and attempted to resolve this. Take a minute to read How to Ask to improve your question. – CharlieRB May 27 '15 at 13:35
  • possible duplicate of Convert unicode to corresponding text in Excel spreadsheet – CharlieRB May 27 '15 at 13:36
  • I believe that this question is not a duplicate, because the older question addresses only the conversion of a single UTF-16 / UCS-2 code point into a character, whereas this question deals with the compositing of a sequence of combining code points into a single glyph. – Scott May 31 '15 at 21:37
1

With data like:

enter image description here

Install the following User Defined Function (UDF):

Public Function qwerty(r As Range) As Variant
   Dim L As Long, CH As String, CH2 As String
   arr = Split(ActiveCell.Text, " ")
   For Each a In arr
      L = Application.WorksheetFunction.Hex2Dec(a)
      CH = ChrW(L)
      CH2 = CH2 & CH
   Next a
   qwerty = CH2
End Function

Then pick some cell, say cell B1, format it to Arial Unicode MS and enter:

=qwerty(A1)

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=qwerty(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

  • I have tried the code, but it is not giving the result as shown. – Ajit Kumar Nayak May 28 '15 at 6:41
  • @AjitKumarNayak Have you used the suggested font?? With the wrong font you will see question marks. – Gary's Student May 28 '15 at 10:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.